Encore OS — Documentation

Generated by npm run db:docs:generate — DO NOT EDIT BY HAND. Change the schema via a migration, then regenerate.

Core: hr_ · Tables: 188 · Source generated: 2026-06-10T02:55:32.169Z

Entity relationships

Tables

`hr_aca_compliance`

RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default	Comment
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`tax_year`	integer	no
`ale_status`	boolean	yes	false
`fte_count`	numeric(10,2)	yes		Full-time equivalent employee count for the tax year (supports fractional FTE).
`total_employee_count`	integer	yes
`forms_1095c_generated`	boolean	yes	false
`forms_1095c_generated_at`	timestamp with time zone	yes
`forms_1094c_generated`	boolean	yes	false
`forms_1094c_generated_at`	timestamp with time zone	yes
`filing_status`	text	yes	’not_started’::text
`filed_at`	timestamp with time zone	yes
`confirmation_number`	text	yes
`notes`	text	yes
`custom_fields`	jsonb	no	’{}‘::jsonb
`created_at`	timestamp with time zone	no	now()
`updated_at`	timestamp with time zone	no	now()
`created_by`	uuid	yes
`updated_by`	uuid	yes

Foreign keys:

created_by → pf_profiles(id)
organization_id → pf_organizations(id)
updated_by → pf_profiles(id)

Policies:

hr_aca_compliance_delete (DELETE)
hr_aca_compliance_insert (INSERT)
hr_aca_compliance_select (SELECT)
hr_aca_compliance_update (UPDATE)

`hr_aca_employee_coverage`

RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`aca_compliance_id`	uuid	no
`employee_id`	uuid	no
`monthly_coverage`	jsonb	no	’[]‘::jsonb
`safe_harbor_code`	text	yes
`form_1095c_generated`	boolean	yes	false
`form_1095c_sent_at`	timestamp with time zone	yes
`custom_fields`	jsonb	no	’{}‘::jsonb
`created_at`	timestamp with time zone	no	now()
`updated_at`	timestamp with time zone	no	now()

Foreign keys:

aca_compliance_id → hr_aca_compliance(id)
employee_id → hr_employees(id)
organization_id → pf_organizations(id)

Policies:

hr_aca_employee_coverage_delete (DELETE)
hr_aca_employee_coverage_insert (INSERT)
hr_aca_employee_coverage_select (SELECT)
hr_aca_employee_coverage_update (UPDATE)

`hr_analytics_cache`

HR-18: Caches expensive analytics computations with TTL RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default	Comment
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`cache_key`	text	no		Unique identifier for cached data within org (e.g., dashboard_summary, trend_12m)
`cache_type`	text	no		Type of cached data: dashboard, report, trend, prediction
`cache_data`	jsonb	no	’{}‘::jsonb
`expires_at`	timestamp with time zone	no		Cache expiration timestamp, controlled by analytics_cache_ttl_minutes setting
`created_at`	timestamp with time zone	no	now()
`updated_at`	timestamp with time zone	no	now()

Foreign keys:

organization_id → pf_organizations(id)

Policies:

hr_analytics_cache_delete_policy (DELETE)
hr_analytics_cache_insert_policy (INSERT)
hr_analytics_cache_select_policy (SELECT)
hr_analytics_cache_update_policy (UPDATE)

`hr_applications`

HR-09: Job applications linking candidates to postings RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`job_posting_id`	uuid	no
`candidate_id`	uuid	no
`status`	hr_application_status	no	’applied’::hr_application_status
`source`	hr_application_source	no	’website’::hr_application_source
`source_detail`	text	yes
`applied_at`	timestamp with time zone	no	now()
`screened_at`	timestamp with time zone	yes
`screened_by`	uuid	yes
`rejection_reason`	text	yes
`rejection_notes`	text	yes
`rejected_at`	timestamp with time zone	yes
`rejected_by`	uuid	yes
`withdrawn_at`	timestamp with time zone	yes
`withdrawal_reason`	text	yes
`hired_at`	timestamp with time zone	yes
`rating`	integer	yes
`notes`	text	yes
`custom_fields`	jsonb	no	’{}‘::jsonb
`created_at`	timestamp with time zone	no	now()
`updated_at`	timestamp with time zone	no	now()
`created_by`	uuid	yes
`updated_by`	uuid	yes

Foreign keys:

candidate_id → hr_candidates(id)
created_by → pf_profiles(id)
job_posting_id → hr_job_postings(id)
organization_id → pf_organizations(id)
rejected_by → pf_profiles(id)
screened_by → pf_profiles(id)
updated_by → pf_profiles(id)

Policies:

hr_applications_delete (DELETE)
hr_applications_insert (INSERT)
hr_applications_select (SELECT)
hr_applications_update (UPDATE)

`hr_availability_preferences`

HR-04: Employee availability patterns RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`employee_id`	uuid	no
`day_of_week`	integer	no
`is_available`	boolean	no	true
`preferred_shift_type`	shift_type	yes
`notes`	text	yes
`effective_date`	date	no	CURRENT_DATE
`end_date`	date	yes
`custom_fields`	jsonb	no	’{}‘::jsonb
`created_at`	timestamp with time zone	no	now()
`updated_at`	timestamp with time zone	no	now()
`created_by`	uuid	no
`updated_by`	uuid	yes

Foreign keys:

created_by → pf_profiles(id)
employee_id → hr_employees(id)
organization_id → pf_organizations(id)
updated_by → pf_profiles(id)

Policies:

Employees and HR admins can delete availability (DELETE)
Employees and HR admins can insert availability (INSERT)
Employees and HR admins can update availability (UPDATE)
Users can view org availability preferences (SELECT)

`hr_background_check_providers`

Background check provider configuration per organization RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`provider_name`	text	no
`display_name`	text	no
`is_active`	boolean	yes	true
`is_default`	boolean	yes	false
`api_key_configured`	boolean	yes	false
`webhook_secret_configured`	boolean	yes	false
`environment`	text	yes	’sandbox’::text
`available_packages`	jsonb	yes	’[]‘::jsonb
`default_package`	text	yes
`total_checks_initiated`	integer	yes	0
`total_checks_completed`	integer	yes	0
`average_turnaround_hours`	numeric(10,2)	yes
`last_webhook_received_at`	timestamp with time zone	yes
`custom_fields`	jsonb	no	’{}‘::jsonb
`created_at`	timestamp with time zone	no	now()
`updated_at`	timestamp with time zone	no	now()
`created_by`	uuid	yes
`updated_by`	uuid	yes
`deleted_at`	timestamp with time zone	yes

Foreign keys:

created_by → pf_profiles(id)
organization_id → pf_organizations(id)
updated_by → pf_profiles(id)

Policies:

hr_background_check_providers_delete (DELETE)
hr_background_check_providers_insert (INSERT)
hr_background_check_providers_select (SELECT)
hr_background_check_providers_update (UPDATE)

`hr_background_check_webhook_dlq`

Dead Letter Queue for failed background check webhook processing RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`original_webhook_id`	uuid	yes
`provider`	text	no	’checkr’::text
`event_type`	text	no
`payload`	jsonb	no
`error_message`	text	no
`error_code`	text	yes
`error_stack`	text	yes
`failed_at`	timestamp with time zone	no	now()
`retry_count`	integer	yes	0
`max_retries`	integer	yes	3
`last_retry_at`	timestamp with time zone	yes
`next_retry_at`	timestamp with time zone	yes
`status`	text	no	’pending’::text
`resolved_at`	timestamp with time zone	yes
`resolved_by`	uuid	yes
`resolution_notes`	text	yes
`created_at`	timestamp with time zone	no	now()
`updated_at`	timestamp with time zone	no	now()
`created_by`	uuid	yes
`updated_by`	uuid	yes
`deleted_at`	timestamp with time zone	yes
`custom_fields`	jsonb	no	’{}‘::jsonb

Foreign keys:

created_by → pf_profiles(id)
organization_id → pf_organizations(id)
original_webhook_id → hr_background_check_webhooks(id)
resolved_by → pf_profiles(id)
updated_by → pf_profiles(id)

Policies:

hr_background_check_webhook_dlq_delete (DELETE)
hr_background_check_webhook_dlq_insert (INSERT)
hr_background_check_webhook_dlq_select (SELECT)
hr_background_check_webhook_dlq_update (UPDATE)

`hr_background_check_webhooks`

RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default	Comment
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`hr_background_check_id`	uuid	no
`provider`	text	no
`provider_request_id`	text	no
`received_at`	timestamp with time zone	no
`signature`	text	yes
`raw_payload`	text	yes		PII SENSITIVE: May contain candidate SSN, DOB, addresses from Checkr/GoodHire webhooks. Subject to FCRA 7-year retention rules (adverse action) or 2-year minimum. Encryption at rest via Supabase default encryption. Access restricted to service-role only for processing. Retention policy: Purge after fcra_retention_until date on hr_background_checks.
`processing_status`	text	no	’received’::text
`error_message`	text	yes
`created_at`	timestamp with time zone	no	now()
`updated_at`	timestamp with time zone	no	now()
`dlq_id`	uuid	yes
`processing_attempts`	integer	yes	0
`last_error`	text	yes
`site_id`	uuid	yes
`created_by`	uuid	yes
`updated_by`	uuid	yes
`deleted_at`	timestamp with time zone	yes
`custom_fields`	jsonb	no	’{}‘::jsonb

Foreign keys:

created_by → pf_profiles(id)
dlq_id → hr_background_check_webhook_dlq(id)
hr_background_check_id → hr_background_checks(id)
organization_id → pf_organizations(id)
site_id → pf_sites(id)
updated_by → pf_profiles(id)

Policies:

hr_background_check_webhooks_deny_delete (DELETE)
hr_background_check_webhooks_insert (INSERT)
hr_background_check_webhooks_select (SELECT)
hr_background_check_webhooks_update (UPDATE)

`hr_background_checks`

Stores Checkr background check records for HR/ATS candidates RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`hr_candidate_id`	uuid	yes
`hr_application_id`	uuid	yes
`checkr_candidate_id`	text	no
`checkr_background_check_id`	text	yes
`checkr_report_id`	text	yes
`checkr_invitation_id`	text	yes
`package_slug`	text	yes
`status`	text	no	’invitation_sent’::text
`result`	text	yes
`invited_at`	timestamp with time zone	yes
`report_completed_at`	timestamp with time zone	yes
`custom_fields`	jsonb	no	’{}‘::jsonb
`created_at`	timestamp with time zone	no	now()
`updated_at`	timestamp with time zone	no	now()
`created_by`	uuid	yes
`updated_by`	uuid	yes
`fcra_disclosure_document_url`	text	yes
`adverse_action_notice_sent_at`	timestamp with time zone	yes
`adverse_action_actor_id`	uuid	yes
`adverse_action_reason_code`	text	yes
`adverse_action_notes`	text	yes
`adverse_action_notice_document_url`	text	yes
`dispute_submitted`	boolean	yes	false
`dispute_reason`	text	yes
`dispute_window_closes_at`	timestamp with time zone	yes
`dispute_resolved_at`	timestamp with time zone	yes
`final_adverse_notice_sent_at`	timestamp with time zone	yes
`final_adverse_notice_document_url`	text	yes
`check_report_url`	text	yes
`fcra_retention_until`	date	yes

Foreign keys:

adverse_action_actor_id → users(id)
created_by → pf_profiles(id)
hr_application_id → hr_applications(id)
hr_candidate_id → hr_candidates(id)
organization_id → pf_organizations(id)
updated_by → pf_profiles(id)

Policies:

hr_background_checks_delete (DELETE)
hr_background_checks_insert (INSERT)
hr_background_checks_select (SELECT)
hr_background_checks_update (UPDATE)

`hr_benefits_dependents`

RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`employee_id`	uuid	no
`first_name`	text	no
`last_name`	text	no
`relationship`	text	no
`date_of_birth`	date	no
`gender`	text	yes
`ssn_encrypted`	text	yes
`ssn_last_four`	text	yes
`is_student`	boolean	no	false
`is_disabled`	boolean	no	false
`school_name`	text	yes
`verification_status`	text	no	’pending’::text
`verification_document_id`	uuid	yes
`verified_by`	uuid	yes
`verified_at`	timestamp with time zone	yes
`is_active`	boolean	no	true
`custom_fields`	jsonb	no	’{}‘::jsonb
`created_at`	timestamp with time zone	no	now()
`created_by`	uuid	no
`updated_at`	timestamp with time zone	no	now()
`updated_by`	uuid	yes

Foreign keys:

created_by → pf_profiles(id)
employee_id → hr_employees(id)
organization_id → pf_organizations(id)
updated_by → pf_profiles(id)
verification_document_id → pf_documents(id)
verified_by → pf_profiles(id)

Policies:

hr_benefits_dependents_delete (DELETE)
hr_benefits_dependents_insert (INSERT)
hr_benefits_dependents_select (SELECT)
hr_benefits_dependents_update (UPDATE)

`hr_benefits_eligibility_rules`

RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`plan_id`	uuid	no
`rule_name`	text	no
`min_tenure_days`	integer	yes
`min_hours_per_week`	numeric(5,2)	yes
`employment_types`	text[]	yes
`job_classifications`	text[]	yes
`departments`	text[]	yes
`sites`	text[]	yes
`exclude_seasonal`	boolean	yes	false
`exclude_temporary`	boolean	yes	false
`age_min`	integer	yes
`age_max`	integer	yes
`is_active`	boolean	no	true
`custom_fields`	jsonb	no	’{}‘::jsonb
`created_at`	timestamp with time zone	no	now()
`created_by`	uuid	no
`updated_at`	timestamp with time zone	no	now()
`updated_by`	uuid	yes

Foreign keys:

created_by → pf_profiles(id)
organization_id → pf_organizations(id)
plan_id → hr_benefits_plans(id)
updated_by → pf_profiles(id)

Policies:

hr_benefits_eligibility_rules_delete (DELETE)
hr_benefits_eligibility_rules_insert (INSERT)
hr_benefits_eligibility_rules_select (SELECT)
hr_benefits_eligibility_rules_update (UPDATE)

`hr_benefits_enrollment_dependents`

RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default	Comment
`id`	uuid	no	gen_random_uuid()
`enrollment_id`	uuid	no
`dependent_id`	uuid	no
`is_covered`	boolean	no	true
`coverage_start_date`	date	yes
`coverage_end_date`	date	yes
`created_at`	timestamp with time zone	no	now()
`organization_id`	uuid	no		Organization ID for multi-tenant isolation - required for RLS
`updated_at`	timestamp with time zone	yes	now()

Foreign keys:

dependent_id → hr_benefits_dependents(id)
enrollment_id → hr_benefits_enrollments(id)
organization_id → pf_organizations(id)

Policies:

hr_benefits_enrollment_dependents_delete (DELETE)
hr_benefits_enrollment_dependents_insert (INSERT)
hr_benefits_enrollment_dependents_select (SELECT)
hr_benefits_enrollment_dependents_update (UPDATE)

`hr_benefits_enrollments`

RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`employee_id`	uuid	no
`plan_id`	uuid	no
`enrollment_period_id`	uuid	yes
`coverage_level`	text	no
`employee_contribution`	numeric(12,2)	no	0
`employer_contribution`	numeric(12,2)	no	0
`effective_date`	date	no
`termination_date`	date	yes
`status`	text	no	’pending’::text
`waiver_reason`	text	yes
`approved_by`	uuid	yes
`approved_at`	timestamp with time zone	yes
`fmla_protected`	boolean	no	false
`fmla_leave_id`	uuid	yes
`enrollment_source`	text	no	’open_enrollment’::text
`qualifying_event_type`	text	yes
`qualifying_event_date`	date	yes
`notes`	text	yes
`custom_fields`	jsonb	no	’{}‘::jsonb
`created_at`	timestamp with time zone	no	now()
`created_by`	uuid	no
`updated_at`	timestamp with time zone	no	now()
`updated_by`	uuid	yes

Foreign keys:

approved_by → pf_profiles(id)
created_by → pf_profiles(id)
employee_id → hr_employees(id)
enrollment_period_id → hr_benefits_open_enrollment_periods(id)
organization_id → pf_organizations(id)
plan_id → hr_benefits_plans(id)
updated_by → pf_profiles(id)

Policies:

hr_benefits_enrollments_delete (DELETE)
hr_benefits_enrollments_insert (INSERT)
hr_benefits_enrollments_select (SELECT)
hr_benefits_enrollments_update (UPDATE)

`hr_benefits_guides`

HR-11: Benefits guide documents uploaded per organization and optionally linked to enrollment periods RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default	Comment
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`enrollment_period_id`	uuid	yes
`title`	text	no
`description`	text	yes
`plan_year`	text	yes
`storage_path`	text	no		Path in hr-documents storage bucket ({orgId}/benefits-guides/{guideId}/{filename})
`file_name`	text	no
`file_size_bytes`	bigint	yes
`uploaded_by`	uuid	yes
`is_active`	boolean	no	true
`custom_fields`	jsonb	no	’{}‘::jsonb	Organization-specific metadata
`created_at`	timestamp with time zone	no	now()
`updated_at`	timestamp with time zone	no	now()
`created_by`	uuid	yes
`updated_by`	uuid	yes

Foreign keys:

created_by → pf_profiles(id)
enrollment_period_id → hr_benefits_open_enrollment_periods(id)
organization_id → pf_organizations(id)
updated_by → pf_profiles(id)
uploaded_by → pf_profiles(id)

Policies:

hr_benefits_guides_delete (DELETE)
hr_benefits_guides_insert (INSERT)
hr_benefits_guides_select (SELECT)
hr_benefits_guides_update (UPDATE)

`hr_benefits_life_events`

RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`employee_id`	uuid	no
`event_type`	text	no
`event_date`	date	no
`description`	text	yes
`documentation_url`	text	yes
`documentation_verified`	boolean	yes	false
`status`	text	no	’pending’::text
`enrollment_window_start`	date	yes
`enrollment_window_end`	date	yes
`reviewed_by`	uuid	yes
`reviewed_at`	timestamp with time zone	yes
`review_notes`	text	yes
`custom_fields`	jsonb	no	’{}‘::jsonb
`created_at`	timestamp with time zone	no	now()
`updated_at`	timestamp with time zone	no	now()
`created_by`	uuid	yes
`updated_by`	uuid	yes

Foreign keys:

created_by → pf_profiles(id)
employee_id → hr_employees(id)
organization_id → pf_organizations(id)
reviewed_by → pf_profiles(id)
updated_by → pf_profiles(id)

Policies:

hr_benefits_life_events_delete (DELETE)
hr_benefits_life_events_insert (INSERT)
hr_benefits_life_events_select (SELECT)
hr_benefits_life_events_update (UPDATE)

`hr_benefits_open_enrollment_periods`

RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default	Comment
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`period_name`	text	no
`description`	text	yes
`start_date`	date	no
`end_date`	date	no
`deadline_date`	date	no
`effective_date`	date	no
`plan_year_start`	date	no
`plan_year_end`	date	no
`is_active`	boolean	no	true
`allow_changes`	boolean	no	true
`notification_sent_at`	timestamp with time zone	yes
`reminder_sent_at`	timestamp with time zone	yes
`custom_fields`	jsonb	no	’{}‘::jsonb
`created_at`	timestamp with time zone	no	now()
`created_by`	uuid	no
`updated_at`	timestamp with time zone	no	now()
`updated_by`	uuid	yes
`require_employee_action`	boolean	no	false	When true, employees who have not completed enrollment or declined coverage will see a blocking gate dialog on login

Foreign keys:

created_by → pf_profiles(id)
organization_id → pf_organizations(id)
updated_by → pf_profiles(id)

Policies:

hr_benefits_open_enrollment_periods_delete (DELETE)
hr_benefits_open_enrollment_periods_insert (INSERT)
hr_benefits_open_enrollment_periods_select (SELECT)
hr_benefits_open_enrollment_periods_update (UPDATE)

`hr_benefits_plans`

RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`name`	text	no
`plan_type`	text	no
`plan_category`	text	yes
`description`	text	yes
`provider_name`	text	yes
`plan_number`	text	yes
`coverage_levels`	jsonb	no	’[]‘::jsonb
`deductible_individual`	numeric(12,2)	yes
`deductible_family`	numeric(12,2)	yes
`out_of_pocket_max_individual`	numeric(12,2)	yes
`out_of_pocket_max_family`	numeric(12,2)	yes
`copay_primary`	numeric(12,2)	yes
`copay_specialist`	numeric(12,2)	yes
`copay_emergency`	numeric(12,2)	yes
`coinsurance_percent`	numeric(5,2)	yes
`employer_contribution_percent`	numeric(5,2)	yes
`employer_match_percent`	numeric(5,2)	yes
`employer_match_limit`	numeric(12,2)	yes
`vesting_schedule`	jsonb	yes
`waiting_period_days`	integer	yes	0
`is_active`	boolean	no	true
`effective_date`	date	no	CURRENT_DATE
`termination_date`	date	yes
`plan_document_id`	uuid	yes
`summary_document_id`	uuid	yes
`custom_fields`	jsonb	no	’{}‘::jsonb
`created_at`	timestamp with time zone	no	now()
`created_by`	uuid	no
`updated_at`	timestamp with time zone	no	now()
`updated_by`	uuid	yes

Foreign keys:

created_by → pf_profiles(id)
organization_id → pf_organizations(id)
plan_document_id → pf_documents(id)
summary_document_id → pf_documents(id)
updated_by → pf_profiles(id)

Policies:

hr_benefits_plans_delete (DELETE)
hr_benefits_plans_insert (INSERT)
hr_benefits_plans_select (SELECT)
hr_benefits_plans_update (UPDATE)

`hr_candidate_communications`

HR candidate communication audit log. TCPA/compliance requires preservation - soft-delete or archive only. RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`candidate_id`	uuid	no
`application_id`	uuid	yes
`template_id`	uuid	yes
`communication_type`	text	no
`subject`	text	yes
`body_text`	text	no
`body_html`	text	yes
`recipient_email`	text	yes
`recipient_phone`	text	yes
`delivery_status`	text	no	’pending’::text
`delivery_provider`	text	yes
`external_message_id`	text	yes
`sent_at`	timestamp with time zone	yes
`delivered_at`	timestamp with time zone	yes
`opened_at`	timestamp with time zone	yes
`clicked_at`	timestamp with time zone	yes
`delivery_error`	text	yes
`bulk_send_id`	uuid	yes
`custom_fields`	jsonb	no	’{}‘::jsonb
`created_at`	timestamp with time zone	no	now()
`created_by`	uuid	yes
`updated_at`	timestamp with time zone	no	now()
`updated_by`	uuid	yes

Foreign keys:

application_id → hr_applications(id)
candidate_id → hr_candidates(id)
created_by → pf_profiles(id)
organization_id → pf_organizations(id)
template_id → hr_communication_templates(id)
updated_by → pf_profiles(id)

Policies:

hr_candidate_communications_delete (DELETE)
hr_candidate_communications_insert (INSERT)
hr_candidate_communications_select (SELECT)
hr_candidate_communications_update (UPDATE)

`hr_candidate_eeo_data`

HR-09: Restricted EEO demographic data separate from general candidate profile access. RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default	Comment
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`candidate_id`	uuid	no
`eeo_gender`	text	yes
`eeo_ethnicity`	text	yes
`eeo_veteran_status`	text	yes
`eeo_disability_status`	text	yes
`source`	text	no	’candidate_self_reported’::text
`collected_at`	timestamp with time zone	no	now()
`custom_fields`	jsonb	no	’{}‘::jsonb	Organization-specific EEO metadata and consent artifacts.
`created_at`	timestamp with time zone	no	now()
`updated_at`	timestamp with time zone	no	now()
`created_by`	uuid	yes
`updated_by`	uuid	yes

Foreign keys:

candidate_id → hr_candidates(id)
created_by → pf_profiles(id)
organization_id → pf_organizations(id)
updated_by → pf_profiles(id)

Policies:

hr_candidate_eeo_data_delete (DELETE)
hr_candidate_eeo_data_insert (INSERT)
hr_candidate_eeo_data_select (SELECT)
hr_candidate_eeo_data_update (UPDATE)

`hr_candidate_portal_accounts`

RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`candidate_id`	uuid	no
`email`	text	no
`email_verified`	boolean	yes	false
`email_verified_at`	timestamp with time zone	yes
`verification_token`	uuid	yes
`verification_token_expires_at`	timestamp with time zone	yes
`last_login_at`	timestamp with time zone	yes
`login_count`	integer	yes	0
`failed_login_attempts`	integer	yes	0
`locked_until`	timestamp with time zone	yes
`sms_opt_in`	boolean	yes	false
`sms_opt_in_at`	timestamp with time zone	yes
`sms_opt_out_at`	timestamp with time zone	yes
`notification_preferences`	jsonb	no	’{“email_status_updates”: true, “sms_interview_reminders”: false, “email_interview_reminders”: true}‘::jsonb
`is_active`	boolean	yes	true
`created_at`	timestamp with time zone	no	now()
`updated_at`	timestamp with time zone	no	now()
`custom_fields`	jsonb	no	’{}‘::jsonb
`created_by`	uuid	yes
`updated_by`	uuid	yes

Foreign keys:

candidate_id → hr_candidates(id)
created_by → pf_profiles(id)
organization_id → pf_organizations(id)
updated_by → pf_profiles(id)

Policies:

hr_candidate_portal_accounts_delete (DELETE)
hr_candidate_portal_accounts_insert (INSERT)
hr_candidate_portal_accounts_select (SELECT)
hr_candidate_portal_accounts_update (UPDATE)

`hr_candidate_portal_sessions`

RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default
`id`	uuid	no	gen_random_uuid()
`portal_account_id`	uuid	no
`token_hash`	text	no
`expires_at`	timestamp with time zone	no
`ip_address`	inet	yes
`user_agent`	text	yes
`created_at`	timestamp with time zone	no	now()
`revoked_at`	timestamp with time zone	yes
`organization_id`	uuid	yes

Foreign keys:

organization_id → pf_organizations(id)
portal_account_id → hr_candidate_portal_accounts(id)

Policies:

hr_candidate_portal_sessions_delete (DELETE)
hr_candidate_portal_sessions_insert (INSERT)
hr_candidate_portal_sessions_select (SELECT)
hr_candidate_portal_sessions_update (UPDATE)

`hr_candidates`

HR-09: Candidate master records (one per email per org) RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default	Comment
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`first_name`	text	no
`last_name`	text	no
`email`	text	no
`phone`	text	yes
`address_line1`	text	yes
`address_city`	text	yes
`address_state`	text	yes
`address_postal_code`	text	yes
`address_country`	text	yes	’USA’::text
`resume_url`	text	yes
`cover_letter_url`	text	yes
`linkedin_url`	text	yes
`portfolio_url`	text	yes
`tags`	text[]	yes	’{}‘::text[]
`notes`	text	yes
`employee_id`	uuid	yes
`custom_fields`	jsonb	no	’{}‘::jsonb
`created_at`	timestamp with time zone	no	now()
`updated_at`	timestamp with time zone	no	now()
`created_by`	uuid	yes
`updated_by`	uuid	yes
`candidate_number`	text	yes		Human-readable candidate identifier (e.g., CAN-000001). Auto-generated on INSERT via trigger.
`deleted_at`	timestamp with time zone	yes		Soft delete timestamp. NULL = active record. Use soft delete functions to delete.

Foreign keys:

created_by → pf_profiles(id)
employee_id → hr_employees(id)
organization_id → pf_organizations(id)
updated_by → pf_profiles(id)

Policies:

hr_candidates_delete (DELETE)
hr_candidates_insert (INSERT)
hr_candidates_select (SELECT)
hr_candidates_update (UPDATE)

`hr_career_paths`

HR-16: Career development paths between positions RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default	Comment
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`from_position_id`	uuid	no
`to_position_id`	uuid	no
`path_type`	text	no	’lateral’::text
`typical_duration_months`	integer	yes
`required_skills`	text[]	yes
`required_certifications`	text[]	yes
`description`	text	yes
`is_active`	boolean	no	true
`custom_fields`	jsonb	no	’{}‘::jsonb	Organization-specific metadata
`created_at`	timestamp with time zone	no	now()
`updated_at`	timestamp with time zone	no	now()
`created_by`	uuid	yes
`updated_by`	uuid	yes
`deleted_at`	timestamp with time zone	yes

Foreign keys:

created_by → pf_profiles(id)
from_position_id → hr_positions(id)
organization_id → pf_organizations(id)
to_position_id → hr_positions(id)
updated_by → pf_profiles(id)

Policies:

hr_career_paths_delete (DELETE)
hr_career_paths_insert (INSERT)
hr_career_paths_select (SELECT)
hr_career_paths_update (UPDATE)

`hr_case_documents`

HR-14: Supporting documents for employee relations cases (storage: hr-employee-relations bucket) RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`entity_type`	text	no
`entity_id`	uuid	no
`file_name`	text	no
`file_path`	text	no
`file_size`	bigint	no
`content_type`	text	no
`category`	text	no	’other’::text
`description`	text	yes
`uploaded_by`	uuid	yes
`custom_fields`	jsonb	no	’{}‘::jsonb
`created_at`	timestamp with time zone	no	now()
`updated_at`	timestamp with time zone	no	now()
`created_by`	uuid	yes
`updated_by`	uuid	yes
`deleted_at`	timestamp with time zone	yes

Foreign keys:

created_by → pf_profiles(id)
organization_id → pf_organizations(id)
updated_by → pf_profiles(id)
uploaded_by → pf_profiles(id)

Policies:

hr_case_documents_delete (DELETE)
hr_case_documents_insert (INSERT)
hr_case_documents_select (SELECT)
hr_case_documents_update (UPDATE)

`hr_clinical_oversight_relationships`

Links supervisors to supervisees for clinical oversight RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default	Comment
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`site_id`	uuid	yes
`supervisor_id`	uuid	no
`supervisee_id`	uuid	no
`requirement_id`	uuid	yes
`relationship_type`	text	no	’supervision’::text
`start_date`	date	no	CURRENT_DATE
`end_date`	date	yes
`status`	text	no	’active’::text
`notes`	text	yes
`custom_fields`	jsonb	no	’{}‘::jsonb
`created_at`	timestamp with time zone	no	now()
`updated_at`	timestamp with time zone	no	now()
`created_by`	uuid	yes
`updated_by`	uuid	yes
`deleted_at`	timestamp with time zone	yes		Soft delete timestamp

Foreign keys:

created_by → users(id)
organization_id → pf_organizations(id)
requirement_id → hr_clinical_oversight_requirements(id)
site_id → pf_sites(id)
supervisee_id → hr_employees(id)
supervisor_id → hr_employees(id)
updated_by → users(id)

Policies:

hr_oversight_relationships_delete (DELETE)
hr_oversight_relationships_insert (INSERT)
hr_oversight_relationships_select (SELECT)
hr_oversight_relationships_update (UPDATE)

`hr_clinical_oversight_requirements`

Defines oversight frequency requirements by credential type RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default	Comment
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`site_id`	uuid	yes
`credential_type_id`	uuid	yes
`oversight_type`	text	no	’supervision’::text
`frequency_days`	integer	no	30
`minimum_hours_per_session`	numeric(4,2)	yes	1.0
`minimum_sessions_per_period`	integer	yes	1
`requires_supervisor_signature`	boolean	yes	true
`requires_supervisee_signature`	boolean	yes	true
`is_active`	boolean	yes	true
`custom_fields`	jsonb	no	’{}‘::jsonb
`created_at`	timestamp with time zone	no	now()
`updated_at`	timestamp with time zone	no	now()
`created_by`	uuid	yes
`updated_by`	uuid	yes
`deleted_at`	timestamp with time zone	yes		Soft delete timestamp

Foreign keys:

created_by → users(id)
credential_type_id → hr_credential_types(id)
organization_id → pf_organizations(id)
site_id → pf_sites(id)
updated_by → users(id)

Policies:

hr_oversight_requirements_delete (DELETE)
hr_oversight_requirements_insert (INSERT)
hr_oversight_requirements_select (SELECT)
hr_oversight_requirements_update (UPDATE)

`hr_clinical_oversight_sessions`

Individual clinical oversight session records RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default	Comment
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`site_id`	uuid	yes
`relationship_id`	uuid	no
`session_date`	date	no
`start_time`	time without time zone	yes
`end_time`	time without time zone	yes
`duration_hours`	numeric(4,2)	yes
`session_type`	text	no	’individual’::text
`location`	text	yes
`topics_covered`	text[]	yes
`goals_discussed`	text	yes
`action_items`	text	yes
`supervisor_notes`	text	yes
`supervisee_notes`	text	yes
`status`	text	no	’scheduled’::text
`supervisor_signature_id`	uuid	yes
`supervisee_signature_id`	uuid	yes
`supervisor_signed_at`	timestamp with time zone	yes
`supervisee_signed_at`	timestamp with time zone	yes
`custom_fields`	jsonb	no	’{}‘::jsonb
`created_at`	timestamp with time zone	no	now()
`updated_at`	timestamp with time zone	no	now()
`created_by`	uuid	yes
`updated_by`	uuid	yes
`deleted_at`	timestamp with time zone	yes		Soft delete timestamp

Foreign keys:

created_by → users(id)
organization_id → pf_organizations(id)
relationship_id → hr_clinical_oversight_relationships(id)
site_id → pf_sites(id)
supervisee_signature_id → pf_signatures(id)
supervisor_signature_id → pf_signatures(id)
updated_by → users(id)

Policies:

hr_oversight_sessions_delete (DELETE)
hr_oversight_sessions_insert (INSERT)
hr_oversight_sessions_select (SELECT)
hr_oversight_sessions_update (UPDATE)

`hr_cobra_elections`

RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`cobra_event_id`	uuid	no
`beneficiary_name`	text	no
`beneficiary_type`	text	no
`beneficiary_email`	text	yes
`beneficiary_phone`	text	yes
`elected`	boolean	yes
`elected_date`	date	yes
`elected_plan_ids`	uuid[]	yes
`monthly_premium`	numeric(10,2)	yes
`last_payment_date`	date	yes
`next_payment_due`	date	yes
`payment_status`	text	yes	’pending’::text
`grace_period_end`	date	yes
`coverage_start_date`	date	yes
`coverage_end_date`	date	yes
`termination_reason`	text	yes
`custom_fields`	jsonb	no	’{}‘::jsonb
`created_at`	timestamp with time zone	no	now()
`updated_at`	timestamp with time zone	no	now()

Foreign keys:

cobra_event_id → hr_cobra_events(id)
organization_id → pf_organizations(id)

Policies:

hr_cobra_elections_delete (DELETE)
hr_cobra_elections_insert (INSERT)
hr_cobra_elections_select (SELECT)
hr_cobra_elections_update (UPDATE)

`hr_cobra_events`

RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`employee_id`	uuid	no
`qualifying_event`	text	no
`event_date`	date	no
`initial_notice_date`	date	yes
`election_notice_sent_at`	timestamp with time zone	yes
`election_deadline`	date	no
`premium_first_due_date`	date	yes
`status`	text	no	’pending_notice’::text
`coverage_start_date`	date	yes
`coverage_end_date`	date	yes
`monthly_premium`	numeric(10,2)	yes
`admin_fee_percent`	numeric(5,2)	yes	2.00
`notes`	text	yes
`custom_fields`	jsonb	no	’{}‘::jsonb
`created_at`	timestamp with time zone	no	now()
`updated_at`	timestamp with time zone	no	now()
`created_by`	uuid	yes
`updated_by`	uuid	yes

Foreign keys:

created_by → pf_profiles(id)
employee_id → hr_employees(id)
organization_id → pf_organizations(id)
updated_by → pf_profiles(id)

Policies:

hr_cobra_events_delete (DELETE)
hr_cobra_events_insert (INSERT)
hr_cobra_events_select (SELECT)
hr_cobra_events_update (UPDATE)

`hr_communication_templates`

RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`template_name`	text	no
`template_type`	text	no
`template_category`	text	yes
`subject`	text	yes
`body_text`	text	no
`body_html`	text	yes
`available_variables`	text[]	yes	ARRAY[‘{{candidate_name}}‘::text, ‘{{candidate_first_name}}‘::text, ‘{{candidate_email}}‘::text, ‘{{position_title}}‘::text, ‘{{department}}‘::text, ‘{{company_name}}‘::text, ‘{{interview_date}}‘::text, ‘{{interview_time}}‘::text, ‘{{interviewer_name}}‘::text, ‘{{application_status}}‘::text, ‘{{portal_link}}‘::text]
`is_active`	boolean	yes	true
`is_default`	boolean	yes	false
`custom_fields`	jsonb	no	’{}‘::jsonb
`created_at`	timestamp with time zone	no	now()
`updated_at`	timestamp with time zone	no	now()
`created_by`	uuid	yes
`updated_by`	uuid	yes

Foreign keys:

created_by → pf_profiles(id)
organization_id → pf_organizations(id)
updated_by → pf_profiles(id)

Policies:

hr_communication_templates_delete (DELETE)
hr_communication_templates_insert (INSERT)
hr_communication_templates_select (SELECT)
hr_communication_templates_update (UPDATE)

`hr_compensation_analyses`

Compensation analysis records for internal equity and market comparisons RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`analysis_name`	text	no
`analysis_type`	text	no
`position_id`	uuid	yes
`department_id`	uuid	yes
`analysis_date`	date	no	CURRENT_DATE
`status`	text	no	’draft’::text
`findings`	text	yes
`recommendations`	text	yes
`equity_score`	numeric	yes
`disparity_percent`	numeric	yes
`custom_fields`	jsonb	no	’{}‘::jsonb
`created_at`	timestamp with time zone	no	now()
`updated_at`	timestamp with time zone	no	now()
`created_by`	uuid	yes
`updated_by`	uuid	yes
`reviewed_by`	uuid	yes
`reviewed_at`	timestamp with time zone	yes
`approved_by`	uuid	yes
`approved_at`	timestamp with time zone	yes

Foreign keys:

approved_by → pf_profiles(id)
created_by → pf_profiles(id)
department_id → pf_departments(id)
organization_id → pf_organizations(id)
position_id → hr_positions(id)
reviewed_by → pf_profiles(id)
updated_by → pf_profiles(id)

Policies:

hr_compensation_analyses_delete (DELETE)
hr_compensation_analyses_insert (INSERT)
hr_compensation_analyses_select (SELECT)
hr_compensation_analyses_update (UPDATE)

`hr_competency_framework_skills`

Skills within a competency framework RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default	Comment
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`framework_id`	uuid	no
`skill_id`	uuid	no
`required_level`	text	no	’intermediate’::text
`is_mandatory`	boolean	no	true
`weight`	integer	yes	1
`display_order`	integer	yes
`custom_fields`	jsonb	no	’{}‘::jsonb
`created_at`	timestamp with time zone	no	now()
`updated_at`	timestamp with time zone	no	now()
`created_by`	uuid	yes
`updated_by`	uuid	yes
`deleted_at`	timestamp with time zone	yes
`pathway_group`	text	yes		HR-13-EN-08: Group key for alternative qualification pathways (e.g., path_a, path_b).
`pathway_logic`	text	no	’required’::text	HR-13-EN-08: Pathway evaluation mode: required or one_of_group.

Foreign keys:

created_by → pf_profiles(id)
framework_id → hr_competency_frameworks(id)
organization_id → pf_organizations(id)
skill_id → hr_skills_library(id)
updated_by → pf_profiles(id)

Policies:

hr_competency_framework_skills_delete (DELETE)
hr_competency_framework_skills_insert (INSERT)
hr_competency_framework_skills_select (SELECT)
hr_competency_framework_skills_update (UPDATE)

`hr_competency_frameworks`

Role-based competency models RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default	Comment
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`position_id`	uuid	yes
`name`	text	no
`description`	text	yes
`is_active`	boolean	no	true
`custom_fields`	jsonb	no	’{}‘::jsonb
`created_at`	timestamp with time zone	no	now()
`updated_at`	timestamp with time zone	no	now()
`created_by`	uuid	yes
`updated_by`	uuid	yes
`deleted_at`	timestamp with time zone	yes
`framework_type`	text	no	’general’::text	HR-13-EN-08: Framework discriminator: general or qualification_pathway.
`regulatory_source`	text	yes		HR-13-EN-08: Jurisdiction-scoped regulatory template source key (PF-96).

Foreign keys:

created_by → pf_profiles(id)
organization_id → pf_organizations(id)
position_id → hr_positions(id)
updated_by → pf_profiles(id)

Policies:

hr_competency_frameworks_delete (DELETE)
hr_competency_frameworks_insert (INSERT)
hr_competency_frameworks_select (SELECT)
hr_competency_frameworks_update (UPDATE)

`hr_contractor_classification_tests`

HR-34: IRS worker classification assessment records RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default	Comment
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`contractor_id`	uuid	no
`test_date`	date	no	CURRENT_DATE
`behavioral_control`	jsonb	yes	’{}‘::jsonb	IRS factor: instructions, training, evaluation
`financial_control`	jsonb	yes	’{}‘::jsonb	IRS factor: investment, expenses, profit/loss opportunity
`relationship_type`	jsonb	yes	’{}‘::jsonb	IRS factor: contracts, benefits, permanency, key activity
`overall_classification`	text	yes		Result: independent_contractor, employee_reclassify
`risk_level`	text	yes		Risk: low, moderate, high
`rationale`	text	yes
`assessed_by`	uuid	yes
`reviewed_by`	uuid	yes
`reviewed_at`	timestamp with time zone	yes
`next_review_date`	date	yes
`custom_fields`	jsonb	no	’{}‘::jsonb	Organization-specific metadata
`created_at`	timestamp with time zone	no	now()
`updated_at`	timestamp with time zone	no	now()
`created_by`	uuid	yes
`updated_by`	uuid	yes

Foreign keys:

assessed_by → users(id)
contractor_id → hr_contractors(id)
created_by → users(id)
organization_id → pf_organizations(id)
reviewed_by → users(id)
updated_by → users(id)

Policies:

hr_contractor_classification_tests_delete (DELETE)
hr_contractor_classification_tests_insert (INSERT)
hr_contractor_classification_tests_select (SELECT)
hr_contractor_classification_tests_update (UPDATE)

`hr_contractor_contracts`

HR-34: Contracts and agreements for contractors RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default	Comment
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`contractor_id`	uuid	no
`contract_type`	text	no	’independent_contractor_agreement’::text	Type: independent_contractor_agreement, sow, amendment, renewal
`title`	text	no
`start_date`	date	yes
`end_date`	date	yes
`auto_renew`	boolean	no	false
`renewal_notice_days`	integer	yes	30
`total_value`	numeric(12,2)	yes
`rate_type`	text	yes		Rate type: hourly, daily, fixed, per_diem
`rate_amount`	numeric(10,2)	yes
`document_storage_path`	text	yes
`status`	text	no	’draft’::text	Status: draft, active, expired, terminated, renewed
`signed_date`	date	yes
`custom_fields`	jsonb	no	’{}‘::jsonb	Organization-specific metadata
`created_at`	timestamp with time zone	no	now()
`updated_at`	timestamp with time zone	no	now()
`created_by`	uuid	yes
`updated_by`	uuid	yes

Foreign keys:

contractor_id → hr_contractors(id)
created_by → users(id)
organization_id → pf_organizations(id)
updated_by → users(id)

Policies:

hr_contractor_contracts_delete (DELETE)
hr_contractor_contracts_insert (INSERT)
hr_contractor_contracts_select (SELECT)
hr_contractor_contracts_update (UPDATE)

`hr_contractor_credentials`

HR-34: Contractor license/certification records (parallel to hr_employee_credentials) RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default	Comment
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`contractor_id`	uuid	no
`credential_type_id`	uuid	no
`license_number`	text	yes
`issuing_authority`	text	yes
`issued_date`	date	yes
`expiration_date`	date	yes
`status`	text	no	’pending_verification’::text	Status: active, expired, pending_verification, revoked
`document_storage_path`	text	yes
`verification_notes`	text	yes
`custom_fields`	jsonb	no	’{}‘::jsonb	Organization-specific metadata
`created_at`	timestamp with time zone	no	now()
`updated_at`	timestamp with time zone	no	now()
`created_by`	uuid	yes
`updated_by`	uuid	yes

Foreign keys:

contractor_id → hr_contractors(id)
created_by → users(id)
credential_type_id → hr_credential_types(id)
organization_id → pf_organizations(id)
updated_by → users(id)

Policies:

hr_contractor_credentials_delete (DELETE)
hr_contractor_credentials_insert (INSERT)
hr_contractor_credentials_select (SELECT)
hr_contractor_credentials_update (UPDATE)

`hr_contractor_time_entries`

HR-34: Contractor time/billing entries with approval workflow RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default	Comment
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`contractor_id`	uuid	no
`contract_id`	uuid	yes
`work_date`	date	no
`hours`	numeric(6,2)	no
`rate`	numeric(10,2)	no
`amount`	numeric(12,2)	no		Computed: hours × rate
`description`	text	yes
`approval_status`	text	no	’pending’::text	Status: pending, approved, rejected
`approved_by`	uuid	yes
`approved_at`	timestamp with time zone	yes
`invoice_id`	text	yes
`custom_fields`	jsonb	no	’{}‘::jsonb	Organization-specific metadata
`created_at`	timestamp with time zone	no	now()
`updated_at`	timestamp with time zone	no	now()
`created_by`	uuid	yes
`updated_by`	uuid	yes

Foreign keys:

approved_by → users(id)
contract_id → hr_contractor_contracts(id)
contractor_id → hr_contractors(id)
created_by → users(id)
organization_id → pf_organizations(id)
updated_by → users(id)

Policies:

hr_contractor_time_entries_delete (DELETE)
hr_contractor_time_entries_insert (INSERT)
hr_contractor_time_entries_select (SELECT)
hr_contractor_time_entries_update (UPDATE)

`hr_contractors`

HR-34: Contractor and contingent workforce profiles RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default	Comment
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`first_name`	text	no
`last_name`	text	no
`email`	text	yes
`phone`	text	yes
`tax_id_encrypted`	text	yes		Encrypted TIN/SSN for 1099 reporting
`tax_id_type`	text	yes		Tax ID type: ssn, ein
`classification`	text	no	’independent_contractor’::text	Worker classification: independent_contractor, agency_contractor, prn_staff, locum_tenens
`w9_on_file`	boolean	no	false
`w9_storage_path`	text	yes
`status`	text	no	’pending_onboarding’::text	Lifecycle status: active, inactive, pending_onboarding, terminated
`start_date`	date	yes
`end_date`	date	yes
`agency_id`	uuid	yes
`department_id`	uuid	yes
`specialty`	text	yes
`hourly_rate`	numeric(10,2)	yes
`notes`	text	yes
`custom_fields`	jsonb	no	’{}‘::jsonb	Organization-specific metadata
`created_at`	timestamp with time zone	no	now()
`updated_at`	timestamp with time zone	no	now()
`created_by`	uuid	yes
`updated_by`	uuid	yes

Foreign keys:

agency_id → hr_staffing_agencies(id)
created_by → users(id)
department_id → pf_departments(id)
organization_id → pf_organizations(id)
updated_by → users(id)

Policies:

hr_contractors_delete (DELETE)
hr_contractors_insert (INSERT)
hr_contractors_select (SELECT)
hr_contractors_update (UPDATE)

`hr_credential_alerts`

HR-02: Logs expiration alerts sent to employees RLS: ✅ enabled (2 policies)

Column	Type	Nullable	Default
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`employee_credential_id`	uuid	no
`alert_type`	text	no
`days_until_expiration`	integer	yes
`notification_id`	uuid	yes
`sent_at`	timestamp with time zone	no	now()
`created_at`	timestamp with time zone	no	now()
`updated_at`	timestamp with time zone	yes	now()

Foreign keys:

employee_credential_id → hr_employee_credentials(id)
notification_id → pf_notifications(id)
organization_id → pf_organizations(id)

Policies:

Users can view related credential alerts (SELECT)
hr_credential_alerts_org_insert (INSERT)

`hr_credential_renewal_workflows`

RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`credential_id`	uuid	no
`employee_id`	uuid	no
`approval_request_id`	uuid	yes
`status`	text	no	’awaiting_employee’::text
`new_credential_number`	text	yes
`new_issuing_authority`	text	yes
`new_issue_date`	date	yes
`new_expiration_date`	date	yes
`new_document_id`	uuid	yes
`employee_notes`	text	yes
`triggered_at`	timestamp with time zone	no	now()
`employee_submitted_at`	timestamp with time zone	yes
`manager_approved_at`	timestamp with time zone	yes
`manager_approved_by`	uuid	yes
`manager_notes`	text	yes
`hr_approved_at`	timestamp with time zone	yes
`hr_approved_by`	uuid	yes
`hr_notes`	text	yes
`completed_at`	timestamp with time zone	yes
`cancelled_at`	timestamp with time zone	yes
`cancelled_by`	uuid	yes
`cancellation_reason`	text	yes
`renewed_credential_id`	uuid	yes
`custom_fields`	jsonb	no	’{}‘::jsonb
`created_at`	timestamp with time zone	yes	now()
`updated_at`	timestamp with time zone	yes	now()
`created_by`	uuid	yes
`updated_by`	uuid	yes

Foreign keys:

approval_request_id → fw_approval_requests(id)
cancelled_by → pf_profiles(id)
created_by → pf_profiles(id)
credential_id → hr_employee_credentials(id)
employee_id → hr_employees(id)
hr_approved_by → pf_profiles(id)
manager_approved_by → pf_profiles(id)
new_document_id → pf_documents(id)
organization_id → pf_organizations(id)
renewed_credential_id → hr_employee_credentials(id)
updated_by → pf_profiles(id)

Policies:

hr_credential_renewal_workflows_delete (DELETE)
hr_credential_renewal_workflows_insert (INSERT)
hr_credential_renewal_workflows_select (SELECT)
hr_credential_renewal_workflows_update (UPDATE)

`hr_credential_requirements`

HR-02: Maps required credentials to positions or departments RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default	Comment
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`position_id`	uuid	yes
`department_id`	uuid	yes
`credential_type_id`	uuid	no
`is_required`	boolean	no	true
`grace_period_days`	integer	yes	0
`notes`	text	yes
`created_at`	timestamp with time zone	no	now()
`updated_at`	timestamp with time zone	no	now()
`created_by`	uuid	no
`updated_by`	uuid	yes
`custom_fields`	jsonb	no	’{}‘::jsonb	Requirement-specific details (e.g., {“state_specific”: “CA”, “waiver_eligible”: false, “verification_frequency_days”: 365, “regulatory_body”: “State Board”, “continuing_ed_hours”: 20})

Foreign keys:

created_by → pf_profiles(id)
credential_type_id → hr_credential_types(id)
organization_id → pf_organizations(id)
position_id → hr_positions(id)
updated_by → pf_profiles(id)

Policies:

Users can view org credential requirements (SELECT)
hr_credential_requirements_delete (DELETE)
hr_credential_requirements_insert (INSERT)
hr_credential_requirements_update (UPDATE)

`hr_credential_types`

HR-02: Defines types of credentials (licenses, certs, training, compliance docs) RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default	Comment
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`name`	text	no
`code`	text	no
`category`	text	no
`description`	text	yes
`is_clinical`	boolean	no	false
`requires_verification`	boolean	no	true
`requires_document`	boolean	no	true
`expires`	boolean	no	true
`default_validity_days`	integer	yes
`renewal_period_days`	integer	yes
`alert_days_before`	integer[]	yes	ARRAY[90, 60, 30, 14, 7]
`is_active`	boolean	no	true
`created_at`	timestamp with time zone	no	now()
`updated_at`	timestamp with time zone	no	now()
`created_by`	uuid	no
`updated_by`	uuid	yes
`custom_fields`	jsonb	no	’{}‘::jsonb	Organization-specific custom data (e.g., {“issuing_states”: [“CA”, “NY”], “ceu_hours”: 12})
`requires_ce`	boolean	no	false	GR-02-EN-01: When true, training_completed events refresh ce_hours_completed on matching credentials.

Foreign keys:

created_by → pf_profiles(id)
organization_id → pf_organizations(id)
updated_by → pf_profiles(id)

Policies:

HR admins can delete credential types (DELETE)
HR admins can insert credential types (INSERT)
HR admins can update credential types (UPDATE)
Users can view org credential types (SELECT)

`hr_deduction_types`

HR-PAY-02: Organization-defined payroll deduction categories (pre-tax, post-tax, garnishment, voluntary) RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`name`	text	no
`code`	text	no
`category`	text	no
`description`	text	yes
`default_amount`	numeric(10,2)	yes
`default_percentage`	numeric(6,4)	yes
`annual_limit`	numeric(12,2)	yes
`is_employer_match`	boolean	yes	false
`employer_match_rate`	numeric(6,4)	yes
`employer_match_limit`	numeric(12,2)	yes
`is_active`	boolean	no	true
`custom_fields`	jsonb	no	’{}‘::jsonb
`created_at`	timestamp with time zone	no	now()
`updated_at`	timestamp with time zone	no	now()

Foreign keys:

organization_id → pf_organizations(id)

Policies:

hr_deduction_types_delete (DELETE)
hr_deduction_types_insert (INSERT)
hr_deduction_types_select (SELECT)
hr_deduction_types_update (UPDATE)

`hr_disciplinary_actions`

HR-14: Disciplinary actions including warnings, suspensions, and terminations RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default	Comment
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`employee_id`	uuid	no
`incident_id`	uuid	yes
`action_type`	text	no
`action_date`	date	no
`effective_date`	date	yes
`end_date`	date	yes
`reason`	text	no
`violation_description`	text	yes
`policy_violated`	text	yes		Reference to company policy or rule violated
`action_taken`	text	no
`expected_improvement`	text	yes
`follow_up_date`	date	yes
`status`	text	no	’active’::text
`outcome`	text	yes
`approved_by`	uuid	yes
`approved_at`	timestamp with time zone	yes
`notes`	text	yes
`custom_fields`	jsonb	no	’{}‘::jsonb	Organization-specific metadata for disciplinary actions
`created_at`	timestamp with time zone	no	now()
`updated_at`	timestamp with time zone	no	now()
`created_by`	uuid	yes
`updated_by`	uuid	yes
`deleted_at`	timestamp with time zone	yes

Foreign keys:

approved_by → pf_profiles(id)
created_by → pf_profiles(id)
employee_id → hr_employees(id)
incident_id → hr_incidents(id)
organization_id → pf_organizations(id)
updated_by → pf_profiles(id)

Policies:

hr_disciplinary_actions_delete (DELETE)
hr_disciplinary_actions_insert (INSERT)
hr_disciplinary_actions_select (SELECT)
hr_disciplinary_actions_update (UPDATE)

`hr_document_access_logs`

HR-12: Immutable audit log for document access (view/download) for compliance tracking RLS: ✅ enabled (2 policies)

Column	Type	Nullable	Default
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`employee_id`	uuid	no
`document_id`	uuid	yes
`document_type`	text	no
`document_title`	text	no
`access_type`	text	no
`accessed_at`	timestamp with time zone	no	now()
`custom_fields`	jsonb	no	’{}‘::jsonb
`created_at`	timestamp with time zone	no	now()
`updated_at`	timestamp with time zone	yes	now()

Foreign keys:

document_id → pf_documents(id)
employee_id → hr_employees(id)
organization_id → pf_organizations(id)

Policies:

hr_doc_access_logs_insert (INSERT)
hr_doc_access_logs_select (SELECT)

`hr_employee_bank_accounts`

HR-PAY-03: Employee direct deposit bank accounts. Encryption handled by edge functions (hr-encrypt-bank-account, hr-decrypt-bank-account). RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default	Comment
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`employee_id`	uuid	no
`bank_name`	text	yes
`routing_number`	text	no
`account_number_encrypted`	text	no		AES-GCM encrypted bank account number (encrypted by hr-encrypt-bank-account edge function)
`account_number_last4`	text	no
`account_type`	text	no
`plaid_account_id`	text	yes
`plaid_access_token_encrypted`	text	yes
`verification_method`	text	no	’manual’::text
`verification_status`	text	no	’pending’::text
`verified_at`	timestamp with time zone	yes
`allocation_type`	text	no	’remainder’::text	How to split deposit: percentage (% of net), fixed ($ amount), remainder (what is left after other accounts)
`allocation_amount`	numeric(12,2)	yes
`allocation_percentage`	numeric(5,2)	yes
`priority_order`	integer	no	1
`is_active`	boolean	no	true
`deactivated_at`	timestamp with time zone	yes
`deactivated_by`	uuid	yes
`custom_fields`	jsonb	no	’{}‘::jsonb
`created_at`	timestamp with time zone	no	now()
`updated_at`	timestamp with time zone	no	now()
`created_by`	uuid	yes
`updated_by`	uuid	yes
`deleted_at`	timestamp with time zone	yes		Soft-delete timestamp. Records with deleted_at IS NOT NULL are considered deleted. Use deactivated_at for user-initiated deactivation vs deleted_at for administrative deletion.

Foreign keys:

created_by → users(id)
deactivated_by → users(id)
employee_id → hr_employees(id)
organization_id → pf_organizations(id)
updated_by → users(id)

Policies:

hr_employee_bank_accounts_delete (DELETE)
hr_employee_bank_accounts_insert (INSERT)
hr_employee_bank_accounts_select (SELECT)
hr_employee_bank_accounts_update (UPDATE)

`hr_employee_credentials`

HR-02: Individual employee credentials with verification status and expiration tracking RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default	Comment
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`employee_id`	uuid	no
`credential_type_id`	uuid	no
`credential_number`	text	yes
`issuing_authority`	text	yes
`issue_date`	date	no
`expiration_date`	date	yes
`verification_status`	text	no	’pending’::text
`verified_by`	uuid	yes
`verified_at`	timestamp with time zone	yes
`rejection_reason`	text	yes
`document_id`	uuid	yes
`notes`	text	yes
`last_alert_sent_at`	timestamp with time zone	yes
`created_at`	timestamp with time zone	no	now()
`updated_at`	timestamp with time zone	no	now()
`created_by`	uuid	no
`updated_by`	uuid	yes
`custom_fields`	jsonb	no	’{}‘::jsonb	Organization-specific custom data (e.g., {“issuing_state”: “CA”, “ceu_hours_completed”: 8})
`ce_hours_completed`	numeric(6,2)	no	0	GR-02-EN-01: Sum of ceu_credits_awarded over the current renewal cycle. Recomputed by hr-training-credential-refresh edge function.

Foreign keys:

created_by → pf_profiles(id)
credential_type_id → hr_credential_types(id)
document_id → pf_documents(id)
employee_id → hr_employees(id)
organization_id → pf_organizations(id)
updated_by → pf_profiles(id)
verified_by → pf_profiles(id)

Policies:

Employees and HR can insert credentials (INSERT)
HR admins and credential owners can update (UPDATE)
HR admins can delete credentials (DELETE)
Users can view accessible employee credentials (SELECT)

`hr_employee_deductions`

HR-PAY-02: Per-employee deduction assignments with effective dating and garnishment details RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default	Comment
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`employee_id`	uuid	no
`deduction_type_id`	uuid	no
`amount_type`	text	no
`amount`	numeric(10,2)	no
`annual_limit`	numeric(12,2)	yes
`ytd_amount`	numeric(12,2)	yes	0
`effective_date`	date	no
`end_date`	date	yes
`garnishment_type`	text	yes
`garnishment_case_number`	text	yes		Court order case number - legally sensitive, payroll admin access only
`garnishment_issuing_authority`	text	yes		Court or agency issuing the garnishment order
`garnishment_priority`	integer	yes
`garnishment_max_percent`	numeric(5,2)	yes
`benefit_enrollment_id`	uuid	yes
`is_auto_created`	boolean	yes	false
`status`	text	no	’active’::text
`custom_fields`	jsonb	no	’{}‘::jsonb
`created_at`	timestamp with time zone	no	now()
`updated_at`	timestamp with time zone	no	now()
`created_by`	uuid	yes
`updated_by`	uuid	yes

Foreign keys:

created_by → pf_profiles(id)
deduction_type_id → hr_deduction_types(id)
employee_id → hr_employees(id)
organization_id → pf_organizations(id)
updated_by → pf_profiles(id)

Policies:

hr_employee_deductions_delete (DELETE)
hr_employee_deductions_insert (INSERT)
hr_employee_deductions_select (SELECT)
hr_employee_deductions_update (UPDATE)

`hr_employee_eeo_profiles`

HR-42: voluntary workforce EEO demographics, isolated from general personnel records; seeded from candidate EEO on hire. Restricted to compliance/HR-admin via RLS. RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`employee_id`	uuid	no
`eeo_gender`	text	yes
`eeo_ethnicity`	text	yes
`eeo_veteran_status`	text	yes
`eeo_disability_status`	text	yes
`declined_gender`	boolean	no	false
`declined_ethnicity`	boolean	no	false
`source_candidate_id`	uuid	yes
`custom_fields`	jsonb	no	’{}‘::jsonb
`created_at`	timestamp with time zone	no	now()
`updated_at`	timestamp with time zone	no	now()
`created_by`	uuid	yes
`updated_by`	uuid	yes

Foreign keys:

created_by → pf_profiles(id)
employee_id → hr_employees(id)
organization_id → pf_organizations(id)
updated_by → pf_profiles(id)

Policies:

hr_employee_eeo_profiles_delete (DELETE)
hr_employee_eeo_profiles_insert (INSERT)
hr_employee_eeo_profiles_select (SELECT)
hr_employee_eeo_profiles_update (UPDATE)

`hr_employee_leave_balances`

RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`employee_id`	uuid	no
`policy_id`	uuid	no
`current_balance`	numeric(10,2)	no	0
`pending_balance`	numeric(10,2)	no	0
`used_balance`	numeric(10,2)	no	0
`accrued_this_period`	numeric(10,2)	no	0
`period_start_date`	date	no
`period_end_date`	date	no
`last_accrual_date`	date	yes
`custom_fields`	jsonb	no	’{}‘::jsonb
`created_at`	timestamp with time zone	no	now()
`updated_at`	timestamp with time zone	no	now()
`created_by`	uuid	no
`updated_by`	uuid	yes

Foreign keys:

created_by → pf_profiles(id)
employee_id → hr_employees(id)
organization_id → pf_organizations(id)
policy_id → hr_leave_policies(id)
updated_by → pf_profiles(id)

Policies:

HR admins and system can update leave balances (UPDATE)
HR admins can delete leave balances (DELETE)
HR admins can insert leave balances (INSERT)
Users can view accessible leave balances (SELECT)

`hr_employee_preferences`

Employee self-service portal preferences RLS: ✅ enabled (3 policies)

Column	Type	Nullable	Default	Comment
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`employee_id`	uuid	no
`notification_preferences`	jsonb	no	’{“sms_enabled”: false, “email_enabled”: true, “in_app_enabled”: true, “email_pay_stubs”: true, “email_leave_requests”: true, “email_benefits_updates”: true, “email_performance_reviews”: true}‘::jsonb	Employee notification channel and type preferences
`dashboard_layout`	jsonb	no	’{“widgets”: [{“id”: “pto-balance”, “order”: 1, “visible”: true}, {“id”: “next-pay-date”, “order”: 2, “visible”: true}, {“id”: “enrollment-status”, “order”: 3, “visible”: true}, {“id”: “pending-actions”, “order”: 4, “visible”: true}, {“id”: “quick-actions”, “order”: 5, “visible”: true}]}‘::jsonb	Dashboard widget visibility and ordering
`language_preference`	text	no	’en’::text	Preferred language code (ISO 639-1)
`custom_fields`	jsonb	no	’{}‘::jsonb
`created_at`	timestamp with time zone	no	now()
`updated_at`	timestamp with time zone	no	now()
`created_by`	uuid	yes
`updated_by`	uuid	yes

Foreign keys:

created_by → pf_profiles(id)
employee_id → hr_employees(id)
organization_id → pf_organizations(id)
updated_by → pf_profiles(id)

Policies:

hr_employee_preferences_self_insert (INSERT)
hr_employee_preferences_self_select (SELECT)
hr_employee_preferences_self_update (UPDATE)

`hr_employee_sites`

RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default	Comment
`id`	uuid	no	gen_random_uuid()
`employee_id`	uuid	no
`site_id`	uuid	no
`is_primary`	boolean	no	false
`effective_date`	date	no	CURRENT_DATE
`end_date`	date	yes
`created_at`	timestamp with time zone	no	now()
`created_by`	uuid	no
`organization_id`	uuid	no
`updated_by`	uuid	yes		User who last updated this record
`updated_at`	timestamp with time zone	yes	now()

Foreign keys:

created_by → pf_profiles(id)
employee_id → hr_employees(id)
organization_id → pf_organizations(id)
site_id → pf_sites(id)
updated_by → users(id)

Policies:

Users can view employee sites (SELECT)
hr_employee_sites_delete (DELETE)
hr_employee_sites_insert (INSERT)
hr_employee_sites_update (UPDATE)

`hr_employee_skills`

Employee skills inventory with verification tracking RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default	Comment
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`employee_id`	uuid	no
`skill_id`	uuid	no
`proficiency_level`	text	no
`acquired_date`	date	yes
`expiration_date`	date	yes
`verification_status`	text	yes	’self_reported’::text
`verified_date`	date	yes
`verified_by`	uuid	yes
`verification_due_date`	date	yes		Calculated: employee hire_date + position skill requirement verification_due_days
`assessment_id`	uuid	yes
`document_url`	text	yes
`credential_id`	uuid	yes
`notes`	text	yes
`custom_fields`	jsonb	no	’{}‘::jsonb
`created_at`	timestamp with time zone	no	now()
`updated_at`	timestamp with time zone	no	now()
`created_by`	uuid	yes
`updated_by`	uuid	yes
`deleted_at`	timestamp with time zone	yes

Foreign keys:

assessment_id → hr_skills_assessments(id)
created_by → pf_profiles(id)
credential_id → hr_employee_credentials(id)
employee_id → hr_employees(id)
organization_id → pf_organizations(id)
skill_id → hr_skills_library(id)
updated_by → pf_profiles(id)
verified_by → pf_profiles(id)

Policies:

hr_employee_skills_delete (DELETE)
hr_employee_skills_insert (INSERT)
hr_employee_skills_select (SELECT)
hr_employee_skills_update (UPDATE)

`hr_employee_tax_info`

RLS: ✅ enabled (3 policies)

Column	Type	Nullable	Default
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`employee_id`	uuid	no
`federal_filing_status`	text	no	’single’::text
`federal_dependent_amount`	numeric(10,2)	yes	0
`federal_other_income`	numeric(10,2)	yes	0
`federal_deductions`	numeric(10,2)	yes	0
`federal_additional_withholding`	numeric(10,2)	yes	0
`federal_exempt`	boolean	no	false
`federal_two_jobs`	boolean	no	false
`az_withholding_percentage`	numeric(4,2)	yes	2.0
`az_additional_withholding`	numeric(10,2)	yes	0
`az_flat_amount`	numeric(10,2)	yes
`az_exempt`	boolean	no	false
`effective_date`	date	no	CURRENT_DATE
`end_date`	date	yes
`custom_fields`	jsonb	no	’{}‘::jsonb
`created_at`	timestamp with time zone	no	now()
`updated_at`	timestamp with time zone	no	now()
`created_by`	uuid	yes
`updated_by`	uuid	yes

Foreign keys:

created_by → pf_profiles(id)
employee_id → hr_employees(id)
organization_id → pf_organizations(id)
updated_by → pf_profiles(id)

Policies:

hr_employee_tax_info_insert (INSERT)
hr_employee_tax_info_select (SELECT)
hr_employee_tax_info_update (UPDATE)

`hr_employee_teams`

RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default	Comment
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`name`	text	no
`description`	text	yes
`team_lead_id`	uuid	yes
`is_active`	boolean	no	true
`created_at`	timestamp with time zone	no	now()
`updated_at`	timestamp with time zone	no	now()
`created_by`	uuid	no
`updated_by`	uuid	yes
`custom_fields`	jsonb	no	’{}‘::jsonb	Team-specific metadata (e.g., {“shift”: “night”, “on_call_rotation”: true, “team_permissions”: [“facility_access”], “budget_code”: “TEAM-001”, “external_group_id”: “ext-123”})

Foreign keys:

created_by → pf_profiles(id)
organization_id → pf_organizations(id)
team_lead_id → hr_employees(id)
updated_by → pf_profiles(id)

Policies:

Users can view org teams (SELECT)
hr_employee_teams_delete (DELETE)
hr_employee_teams_insert (INSERT)
hr_employee_teams_update (UPDATE)

`hr_employee_transfers`

HR-35: Executed employee position moves with optional HR-15 pay-change reference. RLS: ✅ enabled (3 policies)

Column	Type	Nullable	Default
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`employee_id`	uuid	no
`from_position_id`	uuid	yes
`to_position_id`	uuid	no
`internal_application_id`	uuid	yes
`transfer_type`	text	no
`reason`	text	yes
`effective_date`	date	no
`pay_change_id`	uuid	yes
`from_comp_snapshot`	jsonb	yes
`to_comp_snapshot`	jsonb	yes
`created_at`	timestamp with time zone	no	now()
`updated_at`	timestamp with time zone	no	now()
`created_by`	uuid	yes
`updated_by`	uuid	yes
`custom_fields`	jsonb	no	’{}‘::jsonb

Foreign keys:

created_by → pf_profiles(id)
employee_id → hr_employees(id)
from_position_id → hr_positions(id)
internal_application_id → hr_internal_applications(id)
organization_id → pf_organizations(id)
pay_change_id → hr_merit_increases(id)
to_position_id → hr_positions(id)
updated_by → pf_profiles(id)

Policies:

hr_transfers_insert (INSERT)
hr_transfers_select (SELECT)
hr_transfers_update (UPDATE)

`hr_employees`

RLS: ✅ enabled (5 policies)

Column	Type	Nullable	Default	Comment
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`profile_id`	uuid	no
`employee_number`	text	no		Human-readable employee identifier (e.g., EMP-000001). Auto-generated if enabled in hr_module_settings.
`department_id`	uuid	yes
`position_id`	uuid	yes
`job_title`	text	no
`primary_site_id`	uuid	yes
`manager_id`	uuid	yes
`dotted_line_manager_id`	uuid	yes
`hire_date`	date	no
`termination_date`	date	yes
`employment_type`	employment_type	no	’full_time’::employment_type
`employment_status`	employment_status	no	’active’::employment_status
`fte`	numeric(3,2)	no	1.0
`is_clinical`	boolean	no	false
`is_manager`	boolean	no	false
`is_exempt`	boolean	no	false
`work_email`	text	yes
`work_phone`	text	yes
`work_location`	text	yes		DEPRECATED: Use primary_site_id with enhanced pf_sites instead. Will be removed in future release.
`emergency_contact_name`	text	yes
`emergency_contact_relationship`	text	yes
`emergency_contact_phone`	text	yes
`created_at`	timestamp with time zone	no	now()
`updated_at`	timestamp with time zone	no	now()
`created_by`	uuid	no
`updated_by`	uuid	yes
`custom_fields`	jsonb	no	’{}‘::jsonb	Organization-specific custom data (e.g., {“badge_number”: “12345”, “parking_spot”: “A42”})
`level_id`	uuid	yes		Reference to employee level picklist item. Migrated from is_manager flag.
`home_address`	text	yes		Employee home street address
`home_city`	text	yes		Employee home city
`home_state`	text	yes		Employee home state/province
`home_zip`	text	yes		Employee home postal code
`personal_email`	text	yes		Employee personal email address
`personal_phone`	text	yes		Employee personal phone number
`deleted_at`	timestamp with time zone	yes		Soft delete timestamp. NULL = active record. Use soft delete functions to delete.
`entra_id`	text	yes		Microsoft Entra ID user object ID
`entra_provisioned_at`	timestamp with time zone	yes		Timestamp when Entra ID user was created
`entra_provision_status`	text	yes		Status: pending/provisioned/failed/disabled/deleted
`entra_provision_error`	text	yes		Error message if provisioning failed
`entra_license_assigned_at`	timestamp with time zone	yes		Timestamp when Office 365 license was assigned
`entra_disabled_at`	timestamp with time zone	yes		Timestamp when Entra account was disabled
`entra_deleted_at`	timestamp with time zone	yes		Timestamp when Entra account was deleted
`employment_status_picklist_item_id`	uuid	yes		PF-15 phase 2 dual-read: points to pf_picklist_items where pf_picklists.name = ‘hr_employment_status’. Populated alongside the legacy employment_status ENUM. Will become the source of truth once steps 4-5 of the dual-read migration land; the ENUM column will be dropped after a stabilization window.

Foreign keys:

created_by → pf_profiles(id)
department_id → pf_departments(id)
dotted_line_manager_id → hr_employees(id)
employment_status_picklist_item_id → pf_picklist_items(id)
level_id → pf_picklist_items(id)
manager_id → hr_employees(id)
organization_id → pf_organizations(id)
position_id → hr_positions(id)
primary_site_id → pf_sites(id)
profile_id → pf_profiles(id)
updated_by → pf_profiles(id)

Policies:

Employees can update own limited fields (UPDATE)
HR admins can delete employees (DELETE)
HR admins can insert employees (INSERT)
HR admins can update employees (UPDATE)
Users can view active org employees (SELECT)

`hr_employer_tax_config`

RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default	Comment
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`tax_year`	integer	no
`ein`	text	no
`legal_name`	text	no
`trade_name`	text	yes
`address_line1`	text	no
`address_line2`	text	yes
`city`	text	no
`state`	text	no	’AZ’::text
`zip`	text	no
`futa_rate`	numeric(6,4)	no	0.0600
`futa_wage_base`	numeric(12,2)	no	7000.00
`futa_credit_reduction`	numeric(6,4)	no	0.0540
`az_suta_rate`	numeric(6,4)	no	0.0200
`az_suta_wage_base`	numeric(12,2)	no	8000.00
`az_suta_account_number`	text	yes
`deposit_schedule`	text	yes	’monthly’::text
`custom_fields`	jsonb	no	’{}‘::jsonb
`created_at`	timestamp with time zone	no	now()
`updated_at`	timestamp with time zone	no	now()
`created_by`	uuid	yes
`updated_by`	uuid	yes
`efile_provider`	text	yes		E-file provider name (e.g. fire, iris). Non-secret metadata only; credentials stored in Lovable Cloud Secrets.

Foreign keys:

created_by → pf_profiles(id)
organization_id → pf_organizations(id)
updated_by → pf_profiles(id)

Policies:

hr_employer_tax_config_delete (DELETE)
hr_employer_tax_config_insert (INSERT)
hr_employer_tax_config_select (SELECT)
hr_employer_tax_config_update (UPDATE)

`hr_engagement_action_plans`

HR-17: Tracks improvement action plans based on engagement survey analytics RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default	Comment
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`survey_id`	uuid	yes
`analytics_id`	uuid	yes
`title`	text	no
`description`	text	yes
`target_department_id`	uuid	yes
`target_score_area`	text	yes		Category area being improved, e.g., management, work-life-balance, compensation
`current_score`	numeric(5,2)	yes		Engagement score that triggered creation of this action plan
`target_score`	numeric(5,2)	yes		Goal engagement score to achieve through this action plan
`status`	hr_action_plan_status	no	’draft’::hr_action_plan_status
`priority`	hr_action_plan_priority	no	’medium’::hr_action_plan_priority
`due_date`	date	yes
`completed_at`	timestamp with time zone	yes
`assigned_to`	uuid	yes
`custom_fields`	jsonb	no	’{}‘::jsonb
`created_at`	timestamp with time zone	no	now()
`created_by`	uuid	yes
`updated_at`	timestamp with time zone	no	now()
`updated_by`	uuid	yes

Foreign keys:

analytics_id → hr_engagement_analytics(id)
assigned_to → pf_profiles(id)
created_by → pf_profiles(id)
organization_id → pf_organizations(id)
survey_id → hr_engagement_surveys(id)
target_department_id → pf_departments(id)
updated_by → pf_profiles(id)

Policies:

hr_engagement_action_plans_delete (DELETE)
hr_engagement_action_plans_insert (INSERT)
hr_engagement_action_plans_select (SELECT)
hr_engagement_action_plans_update (UPDATE)

`hr_engagement_analytics`

HR-17: Aggregated engagement analytics and trends RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default	Comment
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`survey_id`	uuid	yes
`analytics_period`	text	no
`period_start_date`	date	no
`period_end_date`	date	no
`overall_engagement_score`	numeric	yes
`response_rate`	numeric	yes
`department_scores`	jsonb	yes	’{}‘::jsonb
`question_scores`	jsonb	yes	’{}‘::jsonb
`previous_period_score`	numeric	yes
`trend_direction`	text	yes
`custom_fields`	jsonb	no	’{}‘::jsonb
`created_at`	timestamp with time zone	no	now()
`updated_at`	timestamp with time zone	no	now()
`created_by`	uuid	yes
`updated_by`	uuid	yes		User who last updated this record

Foreign keys:

created_by → pf_profiles(id)
organization_id → pf_organizations(id)
survey_id → hr_engagement_surveys(id)
updated_by → users(id)

Policies:

hr_engagement_analytics_delete (DELETE)
hr_engagement_analytics_insert (INSERT)
hr_engagement_analytics_select (SELECT)
hr_engagement_analytics_update (UPDATE)

`hr_engagement_surveys`

HR-17: Engagement survey definitions RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default	Comment
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`name`	text	no
`survey_type`	text	no
`description`	text	yes
`is_anonymous`	boolean	no	true	If true, employee_id is nullified in responses
`allow_partial_completion`	boolean	yes	false
`form_id`	uuid	yes		Links to PF-08 Forms for question configuration
`target_audience`	text	yes
`target_department_ids`	uuid[]	yes
`target_site_ids`	uuid[]	yes
`start_date`	date	no
`end_date`	date	no
`reminder_days`	integer[]	yes	ARRAY[7, 3, 1]
`status`	text	yes	’draft’::text
`custom_fields`	jsonb	no	’{}‘::jsonb
`created_at`	timestamp with time zone	no	now()
`updated_at`	timestamp with time zone	no	now()
`created_by`	uuid	yes
`updated_by`	uuid	yes

Foreign keys:

created_by → pf_profiles(id)
form_id → fw_forms(id)
organization_id → pf_organizations(id)
updated_by → pf_profiles(id)

Policies:

hr_engagement_surveys_delete (DELETE)
hr_engagement_surveys_insert (INSERT)
hr_engagement_surveys_select (SELECT)
hr_engagement_surveys_update (UPDATE)

`hr_enrollment_reminder_logs`

HR-11: Tracks enrollment reminder notifications sent to employees RLS: ✅ enabled (2 policies)

Column	Type	Nullable	Default
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`enrollment_period_id`	uuid	no
`employee_id`	uuid	no
`profile_id`	uuid	no
`template_key`	text	no
`sent_by`	uuid	yes
`sent_at`	timestamp with time zone	no	now()
`created_at`	timestamp with time zone	no	now()

Foreign keys:

employee_id → hr_employees(id)
enrollment_period_id → hr_benefits_open_enrollment_periods(id)
organization_id → pf_organizations(id)
profile_id → pf_profiles(id)
sent_by → users(id)

Policies:

hr_enrollment_reminder_logs_insert (INSERT)
hr_enrollment_reminder_logs_select (SELECT)

`hr_entra_sync_log`

Audit log for Entra ID synchronization operations (PF-63 Phase 3) RLS: ✅ enabled (2 policies)

Column	Type	Nullable	Default
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`employee_id`	uuid	yes
`sync_type`	text	no
`sync_status`	text	no
`sync_error`	text	yes
`sync_data`	jsonb	yes
`entra_response`	jsonb	yes
`sync_started_at`	timestamp with time zone	no	now()
`sync_completed_at`	timestamp with time zone	yes
`correlation_id`	text	yes
`created_by`	uuid	yes
`sync_direction`	text	no	’to_entra’::text

Foreign keys:

created_by → pf_profiles(id)
employee_id → hr_employees(id)
organization_id → pf_organizations(id)

Policies:

hr_entra_sync_log_insert (INSERT)
hr_entra_sync_log_select (SELECT)

`hr_entra_sync_pending_changes`

RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default	Comment
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`employee_id`	uuid	yes
`entra_user_id`	text	no
`field_name`	text	no
`current_value`	text	yes
`proposed_value`	text	yes
`source`	text	no
`status`	text	no	’pending’::text
`reviewed_by`	uuid	yes
`reviewed_at`	timestamp with time zone	yes
`created_at`	timestamp with time zone	no	now()
`updated_at`	timestamp with time zone	no	now()
`custom_fields`	jsonb	no	’{}‘::jsonb	Extensible JSONB for org-specific metadata on pending Entra sync changes
`created_by`	uuid	yes
`updated_by`	uuid	yes
`deleted_at`	timestamp with time zone	yes

Foreign keys:

created_by → users(id)
employee_id → hr_employees(id)
organization_id → pf_organizations(id)
reviewed_by → users(id)
updated_by → users(id)

Policies:

hr_entra_sync_pending_org_delete (DELETE)
hr_entra_sync_pending_org_insert (INSERT)
hr_entra_sync_pending_org_select (SELECT)
hr_entra_sync_pending_org_update (UPDATE)

`hr_entra_unmatched_users`

PF-63: Tracks Entra ID users discovered during directory sync that have no matching hr_employees record. Admins can link, create, or dismiss. RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default	Comment
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`entra_user_id`	text	no
`display_name`	text	yes
`given_name`	text	yes
`surname`	text	yes
`email`	text	yes
`user_principal_name`	text	yes
`job_title`	text	yes
`department`	text	yes
`status`	text	no	’pending’::text	pending=awaiting admin action, linked=mapped to existing employee, created=new employee created, dismissed=admin chose to ignore, ignored=system-level ignore
`linked_employee_id`	uuid	yes
`resolved_by`	uuid	yes
`resolved_at`	timestamp with time zone	yes
`resolution_notes`	text	yes
`first_seen_at`	timestamp with time zone	no	now()
`last_seen_at`	timestamp with time zone	no	now()
`sync_count`	integer	no	1	Number of sync cycles this user was seen in - helps identify persistent unmatched users
`created_at`	timestamp with time zone	no	now()
`updated_at`	timestamp with time zone	no	now()
`custom_fields`	jsonb	no	’{}‘::jsonb	Extensible metadata for org-specific fields
`created_by`	uuid	yes
`updated_by`	uuid	yes
`deleted_at`	timestamp with time zone	yes		Soft-delete timestamp; non-null means logically deleted

Foreign keys:

created_by → pf_profiles(id)
linked_employee_id → hr_employees(id)
organization_id → pf_organizations(id)
resolved_by → pf_profiles(id)
updated_by → pf_profiles(id)

Policies:

hr_entra_unmatched_users_delete (DELETE)
hr_entra_unmatched_users_insert (INSERT)
hr_entra_unmatched_users_select (SELECT)
hr_entra_unmatched_users_update (UPDATE)

`hr_everify_audit_events`

HR-26: Append-only E-Verify audit trail; no updates/deletes from app. RLS: ✅ enabled (2 policies)

Column	Type	Nullable	Default
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`case_id`	uuid	no
`actor_id`	uuid	yes
`action`	text	no
`metadata`	jsonb	no	’{}‘::jsonb
`created_at`	timestamp with time zone	no	now()

Foreign keys:

actor_id → pf_profiles(id)
case_id → hr_everify_cases(id)
organization_id → pf_organizations(id)

Policies:

hr_everify_audit_insert (INSERT)
hr_everify_audit_select (SELECT)

`hr_everify_cases`

HR-26: USCIS E-Verify case lifecycle per employee; PII-sensitive — RLS enforced. RLS: ✅ enabled (3 policies)

Column	Type	Nullable	Default	Comment
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`employee_id`	uuid	no
`everify_case_number`	text	yes
`status`	text	no	’pending_creation’::text
`case_created_at`	timestamp with time zone	yes
`i9_completed_at`	timestamp with time zone	yes
`deadline_at`	timestamp with time zone	yes
`document_type`	text	yes
`photo_match_required`	boolean	no	false
`photo_match_result`	text	yes
`tnc_issued_at`	timestamp with time zone	yes
`tnc_referral_date`	date	yes
`tnc_employee_notified`	boolean	no	false
`further_action_notice_sent`	boolean	no	false
`referral_confirmation_sent`	boolean	no	false
`resolution_date`	date	yes
`resolution_code`	text	yes
`notes`	text	yes
`further_action_notice_storage_path`	text	yes
`referral_confirmation_storage_path`	text	yes
`api_response_log`	jsonb	no	’[]‘::jsonb	Sanitized API history; redact SSN and full USCIS payloads before append.
`last_successful_sync_at`	timestamp with time zone	yes
`custom_fields`	jsonb	no	’{}‘::jsonb
`created_at`	timestamp with time zone	no	now()
`updated_at`	timestamp with time zone	no	now()
`created_by`	uuid	yes
`updated_by`	uuid	yes

Foreign keys:

created_by → pf_profiles(id)
employee_id → hr_employees(id)
organization_id → pf_organizations(id)
updated_by → pf_profiles(id)

Policies:

hr_everify_cases_insert (INSERT)
hr_everify_cases_select (SELECT)
hr_everify_cases_update (UPDATE)

`hr_everify_config`

HR-26: Per-organization E-Verify API endpoint, feature flags, and vault key reference (no plaintext secrets). RLS: ✅ enabled (3 policies)

Column	Type	Nullable	Default	Comment
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`company_id`	text	yes
`api_endpoint`	text	no
`credentials_vault_key`	text	yes		Vault secret name or reference; never store API password in this table.
`auto_create_enabled`	boolean	no	false
`polling_interval_hours`	integer	no	24
`is_active`	boolean	no	true
`business_day_holidays`	jsonb	yes
`created_at`	timestamp with time zone	no	now()
`updated_at`	timestamp with time zone	no	now()
`created_by`	uuid	yes
`updated_by`	uuid	yes

Foreign keys:

created_by → pf_profiles(id)
organization_id → pf_organizations(id)
updated_by → pf_profiles(id)

Policies:

hr_everify_config_insert (INSERT)
hr_everify_config_select (SELECT)
hr_everify_config_update (UPDATE)

`hr_exit_interviews`

HR-17: Exit interview records linked to HR-03 offboarding RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default	Comment
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`employee_id`	uuid	no
`offboarding_instance_id`	uuid	yes		Links to HR-03 offboarding workflow
`interview_date`	date	yes
`interview_type`	text	yes	’self_service’::text
`conducted_by`	uuid	yes
`form_submission_id`	uuid	yes
`reason_for_leaving`	text	yes
`feedback_text`	text	yes
`would_recommend`	boolean	yes
`rehire_eligible`	boolean	yes
`status`	text	yes	’pending’::text
`completed_at`	timestamp with time zone	yes
`notes`	text	yes
`custom_fields`	jsonb	no	’{}‘::jsonb
`created_at`	timestamp with time zone	no	now()
`updated_at`	timestamp with time zone	no	now()
`created_by`	uuid	yes
`updated_by`	uuid	yes

Foreign keys:

conducted_by → pf_profiles(id)
created_by → pf_profiles(id)
employee_id → hr_employees(id)
form_submission_id → fw_form_submissions(id)
offboarding_instance_id → hr_offboarding_instances(id)
organization_id → pf_organizations(id)
updated_by → pf_profiles(id)

Policies:

hr_exit_interviews_delete (DELETE)
hr_exit_interviews_insert (INSERT)
hr_exit_interviews_select (SELECT)
hr_exit_interviews_update (UPDATE)

`hr_federal_tax_bracket_defaults`

HR: Global federal payroll tax-bracket template (no organization_id). Copied per-org into hr_federal_tax_brackets by hr_backfill_federal_tax_brackets_for_org(). Source of truth for hr_backfill_federal_tax_brackets_for_all_orgs() and the new-org seed trigger. RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default
`id`	uuid	no	gen_random_uuid()
`tax_year`	integer	no
`filing_status`	text	no
`pay_frequency`	text	no
`bracket_floor`	numeric	no
`bracket_ceiling`	numeric	yes
`base_tax`	numeric	no	0
`marginal_rate`	numeric	no
`created_at`	timestamp with time zone	no	now()
`updated_at`	timestamp with time zone	no	now()

Policies:

hr_federal_tax_bracket_defaults_delete (DELETE)
hr_federal_tax_bracket_defaults_insert (INSERT)
hr_federal_tax_bracket_defaults_select (SELECT)
hr_federal_tax_bracket_defaults_update (UPDATE)

`hr_federal_tax_brackets`

RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`tax_year`	integer	no
`filing_status`	text	no
`pay_frequency`	text	no
`bracket_floor`	numeric(12,2)	no
`bracket_ceiling`	numeric(12,2)	yes
`base_tax`	numeric(12,2)	no	0
`marginal_rate`	numeric(6,4)	no
`created_at`	timestamp with time zone	no	now()
`updated_at`	timestamp with time zone	no	now()
`created_by`	uuid	yes
`updated_by`	uuid	yes

Foreign keys:

created_by → pf_profiles(id)
organization_id → pf_organizations(id)
updated_by → pf_profiles(id)

Policies:

hr_federal_tax_brackets_delete (DELETE)
hr_federal_tax_brackets_insert (INSERT)
hr_federal_tax_brackets_select (SELECT)
hr_federal_tax_brackets_update (UPDATE)

`hr_fica_config`

RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`tax_year`	integer	no
`ss_rate`	numeric(6,4)	no	0.0620
`ss_wage_base`	numeric(12,2)	no	176100.00
`medicare_rate`	numeric(6,4)	no	0.0145
`additional_medicare_rate`	numeric(6,4)	no	0.0090
`additional_medicare_threshold_single`	numeric(12,2)	no	200000.00
`additional_medicare_threshold_married`	numeric(12,2)	no	250000.00
`employer_ss_rate`	numeric(6,4)	no	0.0620
`employer_medicare_rate`	numeric(6,4)	no	0.0145
`created_at`	timestamp with time zone	no	now()
`updated_at`	timestamp with time zone	no	now()
`created_by`	uuid	yes
`updated_by`	uuid	yes

Foreign keys:

created_by → pf_profiles(id)
organization_id → pf_organizations(id)
updated_by → pf_profiles(id)

Policies:

hr_fica_config_delete (DELETE)
hr_fica_config_insert (INSERT)
hr_fica_config_select (SELECT)
hr_fica_config_update (UPDATE)

`hr_fingerprint_clearance_incidents`

HR-28: Incidents (suspension, revocation, arrest, GCE) linked to fingerprint clearance records RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default	Comment
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`clearance_id`	uuid	no
`employee_id`	uuid	no
`incident_type`	text	no
`incident_date`	date	no
`description`	text	yes
`action_taken`	text	yes
`action_date`	date	yes
`resolved`	boolean	no	false
`resolution_notes`	text	yes
`resolved_date`	date	yes
`resolved_by`	uuid	yes
`related_er_case_reference`	text	yes		Optional HR-14 case id or external reference (no FK in MVP)
`custom_fields`	jsonb	no	’{}‘::jsonb	Organization-specific metadata
`created_at`	timestamp with time zone	no	now()
`updated_at`	timestamp with time zone	no	now()
`created_by`	uuid	yes
`updated_by`	uuid	yes

Foreign keys:

clearance_id → hr_fingerprint_clearances(id)
created_by → pf_profiles(id)
employee_id → hr_employees(id)
organization_id → pf_organizations(id)
resolved_by → users(id)
updated_by → pf_profiles(id)

Policies:

hr_fpc_incidents_delete (DELETE)
hr_fpc_incidents_insert (INSERT)
hr_fpc_incidents_select (SELECT)
hr_fpc_incidents_update (UPDATE)

`hr_fingerprint_clearances`

HR-28: Arizona DPS fingerprint clearance cards per employee with status, expiration, and verification tracking RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default	Comment
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`employee_id`	uuid	no
`card_type`	text	no
`card_number`	text	yes		DPS-issued card number (sensitive PII — encrypted at rest; never log raw value)
`status`	text	no
`issue_date`	date	yes
`expiration_date`	date	yes
`application_date`	date	yes
`denial_reason`	text	yes
`suspension_date`	date	yes
`revocation_date`	date	yes
`good_cause_exception`	boolean	no	false
`gce_documentation_path`	text	yes
`card_image_path`	text	yes
`verification_date`	date	yes
`verified_by`	uuid	yes		References auth.users(id) — the user who verified the card (distinct from audit created_by/updated_by which reference pf_profiles)
`alert_90_sent`	boolean	no	false
`alert_60_sent`	boolean	no	false
`alert_30_sent`	boolean	no	false
`notes`	text	yes
`custom_fields`	jsonb	no	’{}‘::jsonb	Organization-specific metadata (e.g., {“external_tracking_id”: “EXT-123”})
`created_at`	timestamp with time zone	no	now()
`updated_at`	timestamp with time zone	no	now()
`created_by`	uuid	yes
`updated_by`	uuid	yes

Foreign keys:

created_by → pf_profiles(id)
employee_id → hr_employees(id)
organization_id → pf_organizations(id)
updated_by → pf_profiles(id)
verified_by → users(id)

Policies:

hr_fingerprint_clearances_delete (DELETE)
hr_fingerprint_clearances_insert (INSERT)
hr_fingerprint_clearances_select (SELECT)
hr_fingerprint_clearances_update (UPDATE)

`hr_fmla_cases`

RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`employee_id`	uuid	no
`case_number`	text	no
`case_type`	fmla_case_type	no
`status`	fmla_case_status	no	’active’::fmla_case_status
`start_date`	date	no
`end_date`	date	yes
`certification_date`	date	yes
`recertification_date`	date	yes
`total_hours_available`	numeric(10,2)	no	480
`total_hours_used`	numeric(10,2)	no	0
`medical_condition`	text	yes
`treating_provider`	text	yes
`certification_document_id`	uuid	yes
`case_notes`	text	yes
`custom_fields`	jsonb	no	’{}‘::jsonb
`created_at`	timestamp with time zone	no	now()
`updated_at`	timestamp with time zone	no	now()
`created_by`	uuid	no
`updated_by`	uuid	yes

Foreign keys:

certification_document_id → pf_documents(id)
created_by → pf_profiles(id)
employee_id → hr_employees(id)
organization_id → pf_organizations(id)
updated_by → pf_profiles(id)

Policies:

HR admins can delete FMLA cases (DELETE)
HR admins can insert FMLA cases (INSERT)
HR admins can update FMLA cases (UPDATE)
HR admins can view FMLA cases (SELECT)

`hr_grievance_assignees`

HR-14: HR staff assigned to handle a grievance (multiple assignees per case) RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`grievance_id`	uuid	no
`profile_id`	uuid	no
`custom_fields`	jsonb	no	’{}‘::jsonb
`created_at`	timestamp with time zone	no	now()
`created_by`	uuid	yes

Foreign keys:

created_by → pf_profiles(id)
grievance_id → hr_grievances(id)
organization_id → pf_organizations(id)
profile_id → pf_profiles(id)

Policies:

hr_grievance_assignees_delete (DELETE)
hr_grievance_assignees_insert (INSERT)
hr_grievance_assignees_select (SELECT)
hr_grievance_assignees_update (UPDATE)

`hr_grievances`

HR-14: Employee grievances and formal complaints RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default	Comment
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`employee_id`	uuid	no
`grievance_type`	text	no
`filed_date`	date	no
`description`	text	no
`status`	text	no	’filed’::text
`resolution`	text	yes
`resolved_date`	date	yes
`resolution_outcome`	text	yes
`investigation_id`	uuid	yes
`assigned_to`	uuid	yes
`notes`	text	yes
`custom_fields`	jsonb	no	’{}‘::jsonb	Organization-specific metadata for grievances
`created_at`	timestamp with time zone	no	now()
`updated_at`	timestamp with time zone	no	now()
`created_by`	uuid	yes
`updated_by`	uuid	yes
`deleted_at`	timestamp with time zone	yes

Foreign keys:

assigned_to → pf_profiles(id)
created_by → pf_profiles(id)
employee_id → hr_employees(id)
investigation_id → hr_investigations(id)
organization_id → pf_organizations(id)
updated_by → pf_profiles(id)

Policies:

hr_grievances_delete (DELETE)
hr_grievances_insert (INSERT)
hr_grievances_select (SELECT)
hr_grievances_update (UPDATE)

`hr_handbook_bundle_items`

HR-43: Ordered policy items inside a handbook bundle. policy_id references gr_policies(id) without FK (cross-core). RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`bundle_id`	uuid	no
`policy_id`	uuid	no
`sort_order`	integer	no	0
`is_required`	boolean	no	true
`custom_fields`	jsonb	no	’{}‘::jsonb
`created_at`	timestamp with time zone	no	now()
`updated_at`	timestamp with time zone	no	now()
`created_by`	uuid	yes
`updated_by`	uuid	yes

Foreign keys:

bundle_id → hr_handbook_bundles(id)
created_by → users(id)
organization_id → pf_organizations(id)
updated_by → users(id)

Policies:

hr_handbook_bundle_items_delete (DELETE)
hr_handbook_bundle_items_insert (INSERT)
hr_handbook_bundle_items_select (SELECT)
hr_handbook_bundle_items_update (UPDATE)

`hr_handbook_bundles`

HR-43: New-hire / required-reading bundle of GR policies. RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`name`	text	no
`description`	text	yes
`is_default_for_onboarding`	boolean	no	false
`onboarding_template_id`	uuid	yes
`custom_fields`	jsonb	no	’{}‘::jsonb
`created_at`	timestamp with time zone	no	now()
`updated_at`	timestamp with time zone	no	now()
`created_by`	uuid	yes
`updated_by`	uuid	yes

Foreign keys:

created_by → users(id)
onboarding_template_id → hr_onboarding_templates(id)
organization_id → pf_organizations(id)
updated_by → users(id)

Policies:

hr_handbook_bundles_delete (DELETE)
hr_handbook_bundles_insert (INSERT)
hr_handbook_bundles_select (SELECT)
hr_handbook_bundles_update (UPDATE)

`hr_hire_transitions`

HR-09: Audit trail for candidate to employee transitions RLS: ✅ enabled (3 policies)

Column	Type	Nullable	Default
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`application_id`	uuid	yes
`candidate_id`	uuid	yes
`offer_id`	uuid	no
`employee_id`	uuid	no
`hire_date`	date	no
`transition_notes`	text	yes
`created_at`	timestamp with time zone	no	now()
`created_by`	uuid	yes
`updated_at`	timestamp with time zone	no	now()
`updated_by`	uuid	yes

Foreign keys:

application_id → hr_applications(id)
candidate_id → hr_candidates(id)
created_by → pf_profiles(id)
employee_id → hr_employees(id)
offer_id → hr_offers(id)
organization_id → pf_organizations(id)
updated_by → pf_profiles(id)

Policies:

hr_hire_transitions_delete (DELETE)
hr_hire_transitions_insert (INSERT)
hr_hire_transitions_select (SELECT)

`hr_holidays`

RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`name`	text	no
`date`	date	no
`is_recurring`	boolean	yes	false
`created_at`	timestamp with time zone	no	now()
`updated_at`	timestamp with time zone	no	now()
`created_by`	uuid	yes
`updated_by`	uuid	yes

Foreign keys:

created_by → pf_profiles(id)
organization_id → pf_organizations(id)
updated_by → pf_profiles(id)

Policies:

hr_holidays_delete (DELETE)
hr_holidays_insert (INSERT)
hr_holidays_select (SELECT)
hr_holidays_update (UPDATE)

`hr_incidents`

HR-14: Workplace incidents, accidents, and safety events RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default	Comment
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`site_id`	uuid	yes
`employee_id`	uuid	yes
`reported_by`	uuid	no		Profile ID of person reporting incident (may differ from involved employee)
`incident_type`	text	no
`severity`	text	no
`incident_date`	date	no
`incident_time`	time without time zone	yes
`location`	text	yes
`description`	text	no
`witnesses`	text[]	yes
`immediate_action_taken`	text	yes
`resolution`	text	yes
`reported_to_authorities`	boolean	yes	false	Whether reported to OSHA or other regulatory agencies
`report_date`	date	yes
`report_reference`	text	yes
`custom_fields`	jsonb	no	’{}‘::jsonb	Organization-specific metadata for incidents
`created_at`	timestamp with time zone	no	now()
`updated_at`	timestamp with time zone	no	now()
`created_by`	uuid	yes
`updated_by`	uuid	yes
`deleted_at`	timestamp with time zone	yes

Foreign keys:

created_by → pf_profiles(id)
employee_id → hr_employees(id)
organization_id → pf_organizations(id)
reported_by → pf_profiles(id)
site_id → pf_sites(id)
updated_by → pf_profiles(id)

Policies:

hr_incidents_delete (DELETE)
hr_incidents_insert (INSERT)
hr_incidents_select (SELECT)
hr_incidents_update (UPDATE)

`hr_internal_application_stage_audit`

RLS: ✅ enabled (2 policies)

Column	Type	Nullable	Default
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`application_id`	uuid	no
`from_status`	text	yes
`to_status`	text	no
`changed_by`	uuid	yes
`changed_at`	timestamp with time zone	no	now()
`metadata`	jsonb	no	’{}‘::jsonb

Foreign keys:

application_id → hr_internal_applications(id)
changed_by → pf_profiles(id)
organization_id → pf_organizations(id)

Policies:

hr_app_audit_insert (INSERT)
hr_app_audit_select (SELECT)

`hr_internal_applications`

HR-35: Employee internal job applications (separate from HR-09 external ATS). RLS: ✅ enabled (3 policies)

Column	Type	Nullable	Default
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`site_id`	uuid	yes
`position_id`	uuid	no
`employee_id`	uuid	no
`current_manager_id`	uuid	yes
`status`	text	no	’submitted’::text
`notify_current_manager`	boolean	no	false
`manager_notified_at`	timestamp with time zone	yes
`cover_note`	text	yes
`skill_match_score`	numeric(5,2)	yes
`disposition_code`	text	yes
`disposition_notes`	text	yes
`submitted_at`	timestamp with time zone	no	now()
`decided_at`	timestamp with time zone	yes
`created_at`	timestamp with time zone	no	now()
`updated_at`	timestamp with time zone	no	now()
`created_by`	uuid	yes
`updated_by`	uuid	yes
`custom_fields`	jsonb	no	’{}‘::jsonb

Foreign keys:

created_by → pf_profiles(id)
current_manager_id → hr_employees(id)
employee_id → hr_employees(id)
organization_id → pf_organizations(id)
position_id → hr_positions(id)
site_id → pf_sites(id)
updated_by → pf_profiles(id)

Policies:

hr_internal_apps_insert (INSERT)
hr_internal_apps_select (SELECT)
hr_internal_apps_update (UPDATE)

`hr_interview_feedback`

HR-09: Structured interviewer feedback RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`interview_id`	uuid	no
`interviewer_id`	uuid	no
`rating_communication`	integer	yes
`rating_technical`	integer	yes
`rating_cultural_fit`	integer	yes
`rating_problem_solving`	integer	yes
`rating_overall`	integer	yes
`recommendation`	hr_feedback_recommendation	yes
`strengths`	text	yes
`areas_for_improvement`	text	yes
`additional_comments`	text	yes
`is_submitted`	boolean	yes	false
`submitted_at`	timestamp with time zone	yes
`custom_fields`	jsonb	no	’{}‘::jsonb
`created_at`	timestamp with time zone	no	now()
`updated_at`	timestamp with time zone	no	now()
`created_by`	uuid	yes
`updated_by`	uuid	yes

Foreign keys:

created_by → pf_profiles(id)
interview_id → hr_interviews(id)
interviewer_id → hr_employees(id)
organization_id → pf_organizations(id)
updated_by → pf_profiles(id)

Policies:

hr_interview_feedback_delete (DELETE)
hr_interview_feedback_insert (INSERT)
hr_interview_feedback_select (SELECT)
hr_interview_feedback_update (UPDATE)

`hr_interview_scorecard_templates`

HR-09-EN1: Reusable templates defining competencies and rating scales for structured interview scorecards. RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default	Comment
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`template_name`	text	no
`position_type`	text	yes
`description`	text	yes
`competencies`	jsonb	no		JSON array: [{id, name, description, weight}]
`rating_scale`	jsonb	no		JSON: {min, max, labels: {1: “Poor”, …}}
`is_default`	boolean	no	false
`is_active`	boolean	no	true
`custom_fields`	jsonb	no	’{}‘::jsonb	Organization-specific metadata.
`created_at`	timestamp with time zone	no	now()
`updated_at`	timestamp with time zone	no	now()
`created_by`	uuid	yes
`updated_by`	uuid	yes

Foreign keys:

created_by → users(id)
organization_id → pf_organizations(id)
updated_by → users(id)

Policies:

hr_scorecard_templates_delete (DELETE)
hr_scorecard_templates_insert (INSERT)
hr_scorecard_templates_select (SELECT)
hr_scorecard_templates_update (UPDATE)

`hr_interview_scorecards`

HR-09-EN1: Per-interviewer scorecard submissions for interviews. Supports blind evaluation via RLS. RLS: ✅ enabled (3 policies)

Column	Type	Nullable	Default	Comment
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`interview_id`	uuid	no
`template_id`	uuid	no
`interviewer_id`	uuid	no
`application_id`	uuid	no
`job_posting_id`	uuid	yes
`scores`	jsonb	no	’[]‘::jsonb	JSON array of per-competency scores: [{competency_id, rating, notes}]
`overall_score`	numeric(4,2)	yes
`recommendation`	text	yes		Final recommendation: strong_hire, hire, no_hire, strong_no_hire. Required on submit.
`strengths`	text	yes
`concerns`	text	yes
`notes`	text	yes
`completed_at`	timestamp with time zone	yes
`is_submitted`	boolean	no	false
`custom_fields`	jsonb	no	’{}‘::jsonb	Organization-specific metadata.
`created_at`	timestamp with time zone	no	now()
`updated_at`	timestamp with time zone	no	now()
`created_by`	uuid	yes
`updated_by`	uuid	yes

Foreign keys:

application_id → hr_applications(id)
created_by → users(id)
interview_id → hr_interviews(id)
interviewer_id → users(id)
job_posting_id → hr_job_postings(id)
organization_id → pf_organizations(id)
template_id, organization_id → hr_interview_scorecard_templates(id, organization_id)
updated_by → users(id)

Policies:

hr_scorecards_insert (INSERT)
hr_scorecards_select (SELECT)
hr_scorecards_update (UPDATE)

`hr_interviews`

HR-09: Interview scheduling and tracking RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`application_id`	uuid	no
`interview_type`	hr_interview_type	no	’video’::hr_interview_type
`status`	hr_interview_status	no	’scheduled’::hr_interview_status
`scheduled_at`	timestamp with time zone	no
`duration_minutes`	integer	yes	60
`timezone`	text	yes	’America/New_York’::text
`location`	text	yes
`video_link`	text	yes
`interviewer_id`	uuid	yes
`interviewer_notes`	text	yes
`completed_at`	timestamp with time zone	yes
`cancelled_at`	timestamp with time zone	yes
`cancellation_reason`	text	yes
`custom_fields`	jsonb	no	’{}‘::jsonb
`created_at`	timestamp with time zone	no	now()
`updated_at`	timestamp with time zone	no	now()
`created_by`	uuid	yes
`updated_by`	uuid	yes

Foreign keys:

application_id → hr_applications(id)
created_by → pf_profiles(id)
interviewer_id → hr_employees(id)
organization_id → pf_organizations(id)
updated_by → pf_profiles(id)

Policies:

hr_interviews_delete (DELETE)
hr_interviews_insert (INSERT)
hr_interviews_select (SELECT)
hr_interviews_update (UPDATE)

`hr_investigations`

HR-14: Investigation case management for incidents, grievances, and disciplinary matters RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default	Comment
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`source_incident_id`	uuid	yes
`source_grievance_id`	uuid	yes
`source_disciplinary_id`	uuid	yes
`investigation_type`	text	no
`opened_date`	date	no
`assigned_to`	uuid	yes
`status`	text	no	’open’::text
`findings`	text	yes
`conclusion`	text	yes
`recommendations`	text	yes
`completed_date`	date	yes
`closed_date`	date	yes
`notes`	text	yes
`custom_fields`	jsonb	no	’{}‘::jsonb	Organization-specific metadata for investigations
`created_at`	timestamp with time zone	no	now()
`updated_at`	timestamp with time zone	no	now()
`created_by`	uuid	yes
`updated_by`	uuid	yes
`deleted_at`	timestamp with time zone	yes
`target_completion_date`	date	yes		HR-14: Target date for completing the investigation (planned; distinct from completed_date)

Foreign keys:

source_disciplinary_id → hr_disciplinary_actions(id)
source_grievance_id → hr_grievances(id)
source_incident_id → hr_incidents(id)
assigned_to → pf_profiles(id)
created_by → pf_profiles(id)
organization_id → pf_organizations(id)
updated_by → pf_profiles(id)

Policies:

hr_investigations_delete (DELETE)
hr_investigations_insert (INSERT)
hr_investigations_select (SELECT)
hr_investigations_update (UPDATE)

`hr_job_board_import_checkpoints`

HR-09-P5.4: Cursor-based import checkpoints for resumable application imports RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default	Comment
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`integration_id`	uuid	no
`import_type`	text	no		applications, candidates, or jobs
`cursor_value`	text	yes		Provider-specific pagination cursor or timestamp for incremental imports
`last_successful_at`	timestamp with time zone	yes
`last_attempted_at`	timestamp with time zone	yes
`last_error`	text	yes
`items_imported`	integer	no	0
`status`	text	no	’idle’::text	idle, running, completed, failed
`custom_fields`	jsonb	no	’{}‘::jsonb	Organization-specific metadata for import configuration
`created_at`	timestamp with time zone	no	now()
`updated_at`	timestamp with time zone	no	now()
`created_by`	uuid	yes
`updated_by`	uuid	yes

Foreign keys:

created_by → users(id)
integration_id → hr_job_board_integrations(id)
organization_id → pf_organizations(id)
updated_by → users(id)

Policies:

hr_job_board_import_checkpoints_delete (DELETE)
hr_job_board_import_checkpoints_insert (INSERT)
hr_job_board_import_checkpoints_select (SELECT)
hr_job_board_import_checkpoints_update (UPDATE)

`hr_job_board_integrations`

HR-09: Job board integration configurations for automated posting RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default	Comment
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`board_type`	text	no
`board_name`	text	no
`is_active`	boolean	no	true
`api_key`	text	no		SENSITIVE: Encrypted at application level before storage. Decryption occurs in edge functions only using ENCRYPTION_KEY from secrets. TODO: Migrate to Supabase Vault for key management.
`api_secret`	text	yes		SENSITIVE: Encrypted at application level before storage. See api_key for details.
`refresh_token`	text	yes		SENSITIVE: OAuth refresh token, encrypted at application level. See api_key for details.
`token_expires_at`	timestamp with time zone	yes
`auto_post_enabled`	boolean	yes	false
`auto_import_enabled`	boolean	yes	false
`sync_frequency_minutes`	integer	yes	60
`last_sync_at`	timestamp with time zone	yes
`last_sync_status`	text	yes
`last_sync_error`	text	yes
`next_sync_at`	timestamp with time zone	yes
`board_settings`	jsonb	no	’{}‘::jsonb
`custom_fields`	jsonb	no	’{}‘::jsonb
`created_at`	timestamp with time zone	no	now()
`updated_at`	timestamp with time zone	no	now()
`created_by`	uuid	yes
`updated_by`	uuid	yes
`credential_id`	uuid	yes
`deleted_at`	timestamp with time zone	yes
`api_key_vault_id`	uuid	yes		PF-75: Reference to encrypted credential in pf_credential_vault
`api_secret_vault_id`	uuid	yes		PF-75: Reference to encrypted credential in pf_credential_vault
`refresh_token_vault_id`	uuid	yes		PF-75: Reference to encrypted credential in pf_credential_vault
`credential_source`	text	no	’vault’::text	HR-09-P5.4: Credential storage source — legacy (plaintext column) or vault (pf_credential_vault)
`circuit_breaker_state`	text	no	’closed’::text	HR-09-P5.4: Circuit breaker state — closed (normal), open (failing), half_open (probing)
`circuit_breaker_opened_at`	timestamp with time zone	yes		HR-09-P5.4: Timestamp when circuit breaker was last opened
`consecutive_failures`	integer	no	0	HR-09-P5.4: Count of consecutive sync failures for circuit breaker logic
`last_error_category`	text	yes		HR-09-P5.4: Classification of last error — transient, permanent, auth, rate_limit
`last_error_at`	timestamp with time zone	yes		HR-09-P5.4: Timestamp of last error occurrence
`oauth_client_id_vault_id`	uuid	yes		Per-integration OAuth client ID (vault). Falls back to LINKEDIN_OAUTH_CLIENT_ID env if null.
`oauth_client_secret_vault_id`	uuid	yes		Per-integration OAuth client secret (vault). Falls back to LINKEDIN_OAUTH_CLIENT_SECRET env if null.
`oauth_access_token_vault_id`	uuid	yes		OAuth access token (vault). Set after OAuth callback completes.
`oauth_refresh_token_vault_id`	uuid	yes		OAuth refresh token (vault). Used to refresh access token before expiry.
`oauth_token_expires_at`	timestamp with time zone	yes
`oauth_scopes`	text[]	yes
`linkedin_organization_urn`	text	yes		LinkedIn organization URN (urn:li:organization:{id}) used as posting context.
`webhook_signature_scheme`	text	no	’hmac_sha256’::text	Webhook signature scheme: hmac_sha256 (Indeed) or linkedin_v2 (LinkedIn).

Foreign keys:

api_key_vault_id → pf_credential_vault(id)
api_secret_vault_id → pf_credential_vault(id)
created_by → pf_profiles(id)
credential_id → pf_credentials(id)
oauth_access_token_vault_id → pf_credential_vault(id)
oauth_client_id_vault_id → pf_credential_vault(id)
oauth_client_secret_vault_id → pf_credential_vault(id)
oauth_refresh_token_vault_id → pf_credential_vault(id)
organization_id → pf_organizations(id)
refresh_token_vault_id → pf_credential_vault(id)
updated_by → pf_profiles(id)

Policies:

hr_job_board_integrations_delete (DELETE)
hr_job_board_integrations_insert (INSERT)
hr_job_board_integrations_select (SELECT)
hr_job_board_integrations_update (UPDATE)

`hr_job_board_postings`

HR-09: Individual job postings tracked per job board RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`job_posting_id`	uuid	no
`integration_id`	uuid	no
`board_posting_id`	text	no
`board_type`	text	no
`posting_status`	text	no	’pending’::text
`last_posted_at`	timestamp with time zone	yes
`last_refreshed_at`	timestamp with time zone	yes
`posted_url`	text	yes
`view_count`	integer	yes	0
`application_count`	integer	yes	0
`click_count`	integer	yes	0
`error_message`	text	yes
`error_count`	integer	yes	0
`board_specific_config`	jsonb	no	’{}‘::jsonb
`custom_fields`	jsonb	no	’{}‘::jsonb
`created_at`	timestamp with time zone	no	now()
`updated_at`	timestamp with time zone	no	now()
`created_by`	uuid	yes
`updated_by`	uuid	yes

Foreign keys:

created_by → pf_profiles(id)
integration_id → hr_job_board_integrations(id)
job_posting_id → hr_job_postings(id)
organization_id → pf_organizations(id)
updated_by → pf_profiles(id)

Policies:

hr_job_board_postings_delete (DELETE)
hr_job_board_postings_insert (INSERT)
hr_job_board_postings_select (SELECT)
hr_job_board_postings_update (UPDATE)

`hr_job_board_webhook_events`

HR-09-P5.4: Webhook idempotency ledger — prevents duplicate processing of provider callbacks RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default	Comment
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`integration_id`	uuid	no
`idempotency_key`	text	no		Provider-generated event ID or computed hash for dedup
`event_type`	text	no
`payload`	jsonb	no	’{}‘::jsonb
`processing_status`	text	no	’received’::text	received, processed, duplicate_ignored, rejected_invalid_signature, rejected_stale_timestamp, failed
`error_message`	text	yes
`received_at`	timestamp with time zone	no	now()
`processed_at`	timestamp with time zone	yes
`created_at`	timestamp with time zone	no	now()

Foreign keys:

integration_id → hr_job_board_integrations(id)
organization_id → pf_organizations(id)

Policies:

hr_job_board_webhook_events_delete (DELETE)
hr_job_board_webhook_events_insert (INSERT)
hr_job_board_webhook_events_select (SELECT)
hr_job_board_webhook_events_update (UPDATE)

`hr_job_description_agreements`

RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`position_id`	uuid	no
`employee_id`	uuid	no
`status`	text	no	’draft’::text
`content_snapshot`	jsonb	no	’{}‘::jsonb
`manager_id`	uuid	yes
`manager_signed_at`	timestamp with time zone	yes
`manager_signature_id`	uuid	yes
`employee_signed_at`	timestamp with time zone	yes
`employee_signature_id`	uuid	yes
`generated_document_id`	uuid	yes
`custom_fields`	jsonb	no	’{}‘::jsonb
`created_at`	timestamp with time zone	no	now()
`updated_at`	timestamp with time zone	no	now()
`created_by`	uuid	yes
`updated_by`	uuid	yes

Foreign keys:

created_by → pf_profiles(id)
employee_id → hr_employees(id)
generated_document_id → pf_documents(id)
manager_id → hr_employees(id)
organization_id → pf_organizations(id)
position_id → hr_positions(id)
updated_by → pf_profiles(id)

Policies:

hr_job_description_agreements_delete (DELETE)
hr_job_description_agreements_insert (INSERT)
hr_job_description_agreements_select (SELECT)
hr_job_description_agreements_update (UPDATE)

`hr_job_postings`

HR-09: Job posting records for ATS RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`position_id`	uuid	yes
`department_id`	uuid	yes
`site_id`	uuid	yes
`title`	text	no
`description`	text	yes
`requirements`	text	yes
`responsibilities`	text	yes
`benefits`	text	yes
`status`	hr_job_posting_status	no	’draft’::hr_job_posting_status
`employment_type`	text	no	’full_time’::text
`salary_min`	numeric(12,2)	yes
`salary_max`	numeric(12,2)	yes
`salary_currency`	text	yes	’USD’::text
`is_salary_visible`	boolean	yes	false
`remote_option`	text	yes	’onsite’::text
`location_city`	text	yes
`location_state`	text	yes
`location_country`	text	yes	’USA’::text
`published_at`	timestamp with time zone	yes
`closes_at`	timestamp with time zone	yes
`views_count`	integer	yes	0
`applications_count`	integer	yes	0
`hiring_manager_id`	uuid	yes
`custom_fields`	jsonb	no	’{}‘::jsonb
`created_at`	timestamp with time zone	no	now()
`updated_at`	timestamp with time zone	no	now()
`created_by`	uuid	yes
`updated_by`	uuid	yes
`default_scorecard_template_id`	uuid	yes

Foreign keys:

created_by → pf_profiles(id)
default_scorecard_template_id → hr_interview_scorecard_templates(id)
department_id → pf_departments(id)
hiring_manager_id → hr_employees(id)
organization_id → pf_organizations(id)
position_id → hr_positions(id)
site_id → pf_sites(id)
updated_by → pf_profiles(id)

Policies:

hr_job_postings_delete (DELETE)
hr_job_postings_insert (INSERT)
hr_job_postings_select (SELECT)
hr_job_postings_update (UPDATE)

`hr_leadership_program_definitions`

HR-16: Organization catalog of leadership development programs RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default	Comment
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`name`	text	no
`program_type`	text	no	’internal’::text
`default_provider`	text	yes
`description`	text	yes
`is_active`	boolean	no	true
`custom_fields`	jsonb	no	’{}‘::jsonb	Organization-specific metadata
`created_at`	timestamp with time zone	no	now()
`updated_at`	timestamp with time zone	no	now()
`created_by`	uuid	yes
`updated_by`	uuid	yes
`deleted_at`	timestamp with time zone	yes

Foreign keys:

created_by → pf_profiles(id)
organization_id → pf_organizations(id)
updated_by → pf_profiles(id)

Policies:

hr_leadership_program_definitions_delete (DELETE)
hr_leadership_program_definitions_insert (INSERT)
hr_leadership_program_definitions_select (SELECT)
hr_leadership_program_definitions_update (UPDATE)

`hr_leadership_programs`

HR-16: Leadership program enrollment and tracking RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default	Comment
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`employee_id`	uuid	no
`program_name`	text	no
`program_type`	text	no	’internal’::text
`status`	text	no	’enrolled’::text
`start_date`	date	yes
`expected_end_date`	date	yes
`actual_end_date`	date	yes
`completion_percentage`	numeric(5,2)	yes	0
`cost`	numeric(12,2)	yes
`sponsor_employee_id`	uuid	yes
`objectives`	text	yes
`outcomes`	text	yes
`notes`	text	yes
`custom_fields`	jsonb	no	’{}‘::jsonb	Organization-specific metadata
`created_at`	timestamp with time zone	no	now()
`updated_at`	timestamp with time zone	no	now()
`created_by`	uuid	yes
`updated_by`	uuid	yes
`deleted_at`	timestamp with time zone	yes
`program_definition_id`	uuid	yes		HR-16: Reference to organization program catalog entry
`provider`	text	yes

Foreign keys:

created_by → pf_profiles(id)
employee_id → hr_employees(id)
organization_id → pf_organizations(id)
program_definition_id → hr_leadership_program_definitions(id)
sponsor_employee_id → hr_employees(id)
updated_by → pf_profiles(id)

Policies:

hr_leadership_programs_delete (DELETE)
hr_leadership_programs_insert (INSERT)
hr_leadership_programs_select (SELECT)
hr_leadership_programs_update (UPDATE)

`hr_leave_accrual_tiers`

RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default	Comment
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`policy_id`	uuid	no
`min_tenure_months`	integer	no	0
`accrual_rate`	numeric(10,2)	no
`max_balance`	numeric(10,2)	yes
`created_at`	timestamp with time zone	no	now()
`updated_at`	timestamp with time zone	no	now()
`custom_fields`	jsonb	no	’{}‘::jsonb	Organization-specific metadata for accrual tiers
`created_by`	uuid	yes		User who created this tier
`updated_by`	uuid	yes		User who last updated this tier

Foreign keys:

created_by → pf_profiles(id)
organization_id → pf_organizations(id)
policy_id → hr_leave_policies(id)
updated_by → pf_profiles(id)

Policies:

hr_leave_accrual_tiers_delete (DELETE)
hr_leave_accrual_tiers_insert (INSERT)
hr_leave_accrual_tiers_select (SELECT)
hr_leave_accrual_tiers_update (UPDATE)

`hr_leave_policies`

RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`name`	text	no
`leave_type`	leave_type	no
`description`	text	yes
`accrual_method`	accrual_method	no
`accrual_rate`	numeric(10,2)	yes
`accrual_frequency`	accrual_frequency	yes
`max_balance`	numeric(10,2)	yes
`carryover_limit`	numeric(10,2)	yes
`requires_approval`	boolean	no	true
`min_notice_days`	integer	yes	0
`max_consecutive_days`	integer	yes
`applies_to_employment_types`	text[]	yes	ARRAY[‘full_time’::text, ‘part_time’::text]
`is_active`	boolean	no	true
`effective_date`	date	no	CURRENT_DATE
`end_date`	date	yes
`custom_fields`	jsonb	no	’{}‘::jsonb
`created_at`	timestamp with time zone	no	now()
`updated_at`	timestamp with time zone	no	now()
`created_by`	uuid	no
`updated_by`	uuid	yes

Foreign keys:

created_by → pf_profiles(id)
organization_id → pf_organizations(id)
updated_by → pf_profiles(id)

Policies:

HR admins can delete leave policies (DELETE)
HR admins can insert leave policies (INSERT)
HR admins can update leave policies (UPDATE)
Users can view org leave policies (SELECT)

`hr_leave_requests`

RLS: ✅ enabled (5 policies)

Column	Type	Nullable	Default
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`employee_id`	uuid	no
`policy_id`	uuid	no
`start_date`	date	no
`end_date`	date	no
`total_hours`	numeric(10,2)	no
`reason`	text	yes
`status`	leave_request_status	no	’pending’::leave_request_status
`submitted_at`	timestamp with time zone	no	now()
`reviewed_at`	timestamp with time zone	yes
`reviewed_by`	uuid	yes
`reviewer_notes`	text	yes
`cancelled_at`	timestamp with time zone	yes
`cancelled_by`	uuid	yes
`cancellation_reason`	text	yes
`custom_fields`	jsonb	no	’{}‘::jsonb
`created_at`	timestamp with time zone	no	now()
`updated_at`	timestamp with time zone	no	now()
`created_by`	uuid	no
`updated_by`	uuid	yes
`fmla_case_id`	uuid	yes
`entra_calendar_event_id`	text	yes
`entra_calendar_synced_at`	timestamp with time zone	yes
`entra_calendar_sync_status`	text	yes

Foreign keys:

cancelled_by → pf_profiles(id)
created_by → pf_profiles(id)
employee_id → hr_employees(id)
fmla_case_id → hr_fmla_cases(id)
organization_id → pf_organizations(id)
policy_id → hr_leave_policies(id)
reviewed_by → pf_profiles(id)
updated_by → pf_profiles(id)

Policies:

Employees can cancel own pending requests (UPDATE)
Employees can create own leave requests (INSERT)
Managers and HR can approve/deny requests (UPDATE)
Users can view accessible leave requests (SELECT)
hr_leave_requests_admin_delete (DELETE)

`hr_merit_increases`

Merit increase records tied to performance reviews RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`employee_id`	uuid	no
`review_id`	uuid	yes
`current_salary`	numeric	no
`increase_percentage`	numeric	no
`increase_amount`	numeric	no
`new_salary`	numeric	no
`currency`	text	no	’USD’::text
`effective_date`	date	no
`status`	text	no	’draft’::text
`justification`	text	yes
`notes`	text	yes
`custom_fields`	jsonb	no	’{}‘::jsonb
`created_at`	timestamp with time zone	no	now()
`updated_at`	timestamp with time zone	no	now()
`created_by`	uuid	yes
`updated_by`	uuid	yes
`approved_by`	uuid	yes
`approved_at`	timestamp with time zone	yes
`applied_at`	timestamp with time zone	yes

Foreign keys:

approved_by → pf_profiles(id)
created_by → pf_profiles(id)
employee_id → hr_employees(id)
organization_id → pf_organizations(id)
review_id → hr_performance_reviews(id)
updated_by → pf_profiles(id)

Policies:

hr_merit_increases_delete (DELETE)
hr_merit_increases_insert (INSERT)
hr_merit_increases_select (SELECT)
hr_merit_increases_update (UPDATE)

`hr_module_settings`

RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default	Comment
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`employee_number_prefix`	text	yes	’EMP-’::text
`employee_number_auto_generate`	boolean	yes	false	When true, automatically generates sequential employee numbers on insert. Default: true
`require_profile_photo`	boolean	yes	false
`default_alert_days`	integer[]	yes	ARRAY[90, 60, 30, 14, 7]
`require_document_for_credentials`	boolean	yes	true
`allow_self_upload`	boolean	yes	true
`grace_period_days`	integer	yes	0
`default_onboarding_template_id`	uuid	yes		Template to use for automatic onboarding creation when auto_create_onboarding is enabled.
`auto_create_onboarding`	boolean	yes	false	When true, automatically creates an onboarding instance when an offer is accepted. Uses default_onboarding_template_id.
`onboarding_due_days`	integer	yes	30
`default_shift_duration_hours`	integer	yes	8
`overtime_threshold_hours`	integer	yes	40
`require_credential_for_shift`	boolean	yes	true
`custom_employment_types`	text[]	yes	’{}‘::text[]
`custom_credential_categories`	text[]	yes	’{}‘::text[]
`created_at`	timestamp with time zone	no	now()
`updated_at`	timestamp with time zone	no	now()
`created_by`	uuid	yes
`updated_by`	uuid	yes
`ai_enabled`	boolean	yes	true	Enable AI features for Workforce/HR module
`offer_approval_required`	boolean	yes	true
`offer_approval_levels`	integer	yes	2
`offer_executive_threshold`	numeric	yes	100000
`source_costs`	jsonb	yes	’{}‘::jsonb	Configuration for recruitment source costs - monthly fees, per-post costs, referral bonuses
`credential_renewal_trigger_days`	integer	yes	60	Days before expiration to auto-trigger renewal workflow (default: 60)
`credential_renewal_enabled`	boolean	yes	true	Enable/disable automatic credential renewal workflows
`use_configurable_wizards`	boolean	yes	false	PF-41: Use configurable wizard templates instead of hardcoded wizards
`adverse_impact_threshold_percent`	numeric(5,2)	yes	80.00	EEOC four-fifths rule threshold (80% recommended)
`high_conversion_rate_threshold_percent`	numeric(5,2)	yes	10.00	Threshold for flagging high conversion rate sources
`default_skill_verification_days`	integer	yes	30	Default days from hire to verify skills if not set on position requirement
`skill_verification_alert_days`	integer[]	yes	ARRAY[14, 7, 3]	Days before verification due date to send alerts
`skill_reassessment_certified_days`	integer	yes	365	Days between re-assessment for certified skills
`skill_reassessment_assessed_days`	integer	yes	730	Days between re-assessment for assessed skills
`skill_reassessment_validated_days`	integer	yes	1095	Days between re-assessment for manager-validated skills
`skills_list_page_size`	integer	yes	50
`auto_create_skill_verification_tasks`	boolean	yes	true
`skills_feature_enabled`	boolean	yes	true	Enable/disable Skills & Competencies feature for this organization
`employee_relations_retention_years`	integer	yes	7	Years to retain incident/disciplinary records for compliance
`grievance_resolution_sla_days`	integer	yes	30	Target days to resolve grievances
`critical_incident_notification_hours`	integer	yes	4	Hours within which critical incidents must be escalated
`investigation_sla_critical_days`	integer	yes	7	Target days to complete critical investigations
`investigation_sla_serious_days`	integer	yes	14	Target days to complete serious investigations
`investigation_sla_moderate_days`	integer	yes	30	Target days to complete moderate investigations
`appeal_resolution_sla_days`	integer	yes	14	Target days to resolve disciplinary appeals
`review_auto_assign_days_before`	integer	yes	30	Days before review deadline to auto-assign reviews to managers
`goal_reminder_interval_days`	integer	yes	30	Days between goal progress update reminders
`feedback_deadline_default_days`	integer	yes	14	Default deadline in days for feedback requests
`pip_review_interval_days`	integer	yes	30	Days between PIP review checkpoints
`default_page_size`	integer	yes	50	Default page size for HR module list views
`performance_page_size`	integer	yes	20	Page size for performance review lists
`ats_page_size`	integer	yes	20	Page size for ATS/recruiting lists
`pip_default_duration_days`	integer	yes	30	Default duration in days for Performance Improvement Plans
`pip_warning_days`	integer	yes	7	Days before PIP deadline to show warning
`goal_deadline_warning_days`	integer	yes	7	Days before goal deadline to show warning
`shifts_lookahead_days`	integer	yes	7	Number of days to show in shift/schedule lookahead
`default_merit_percentage_exceeds`	numeric	yes	5.0	Default merit increase percentage for “Exceeds Expectations” performance rating
`default_merit_percentage_meets`	numeric	yes	3.0	Default merit increase percentage for “Meets Expectations” performance rating
`default_merit_percentage_below`	numeric	yes	0.0	Default merit increase percentage for “Below Expectations” performance rating
`equity_disparity_threshold`	numeric	yes	15.0	Pay disparity percentage threshold that triggers equity alerts
`default_currency`	text	yes	’USD’::text	Default currency code for compensation data (e.g., USD, EUR, GBP)
`survey_default_reminder_days`	integer[]	yes	ARRAY[7, 3, 1]	HR-17: Default reminder days before survey end date
`exit_interview_auto_create`	boolean	yes	true	HR-17: Automatically create exit interview when employee is terminated
`engagement_analytics_auto_calculate`	boolean	yes	false	HR-17: Automatically calculate engagement analytics on survey completion
`use_enhanced_payroll_wizard`	boolean	yes	true	Enable 7-step PF-41 based payroll wizard
`payroll_overtime_threshold_percent`	numeric(5,2)	yes	20.00	Overtime hours threshold for anomaly detection (% of regular hours)
`payroll_variance_threshold_percent`	numeric(5,2)	yes	25.00	Pay variance threshold for anomaly detection (% change from prior period)
`payroll_large_adjustment_threshold`	numeric(10,2)	yes	1000.00	Dollar threshold for flagging large adjustments
`employee_number_sequence`	integer	yes	1
`candidate_number_prefix`	text	yes	’CAN’::text
`candidate_number_sequence`	integer	yes	1
`analytics_cache_ttl_minutes`	integer	yes	15	Cache TTL in minutes for analytics data (default: 15)
`dashboard_refresh_interval_minutes`	integer	yes	15	Auto-refresh interval for dashboard widgets (default: 15)
`retention_risk_high_threshold`	integer	yes	70	Score threshold (0-100) for high retention risk classification (default: 70)
`default_trend_period_months`	integer	yes	12	Default historical period for trend analysis (default: 12)
`max_report_export_rows`	integer	yes	10000	Maximum rows allowed in report exports (default: 10000)
`auto_create_payroll_runs`	boolean	yes	false
`send_deadline_reminders`	boolean	yes	true
`reference_expiration_days`	integer	yes	30	HR-09-P5: Days a reference request access token remains valid (default 30).
`reference_reminder_days`	integer[]	yes	ARRAY[7, 14]	HR-09-P5: Days after request to send follow-up reminders (default {3,7}).
`bgc_auto_initiate_on_offer`	boolean	yes	false	HR-09-P5: Auto-create a background check when an offer is sent.
`bgc_default_package`	text	yes	’standard’::text	HR-09-P5: Provider-specific default BGC package identifier.
`bgc_default_provider_id`	uuid	yes
`sms_notifications_enabled`	boolean	yes	false
`candidate_portal_enabled`	boolean	yes	false	HR-09-P5: Enable external candidate portal.
`job_board_auto_post`	boolean	yes	false	HR-09-P5: Auto-publish new job postings to configured boards.
`job_board_default_boards`	text[]	yes	ARRAY[]::text[]
`references_required_count`	integer	yes	2
`references_required_for_offer`	boolean	yes	false
`reference_form_intro_text`	text	yes
`reference_thank_you_text`	text	yes
`email_provider`	text	no	’entra’::text	Email provider for org-level sending: entra (Microsoft Entra ID) or gmail (Google Workspace).
`email_from_address`	text	yes	’no-reply@example.com’::text	From email address for HR communications (used with Entra)
`email_from_name`	text	yes	’Recruiting’::text	From display name for HR communications
`custom_fields`	jsonb	no	’{}‘::jsonb
`succession_readiness_performance_weight`	integer	yes	50	HR-16: Weight (0-100) for performance in readiness score calculation
`succession_readiness_skills_weight`	integer	yes	50	HR-16: Weight (0-100) for skills in readiness score calculation
`talent_pipeline_review_frequency_days`	integer	yes	90	HR-16: Days between talent pipeline reviews
`max_successors_per_plan`	integer	yes	3	HR-16: Max successors per succession plan
`email_sender_address`	text	yes		Optional per-module email sender address (e.g. hr@domain.com). Falls back to org default if null.
`job_description_min_responsibilities`	integer	no	1	HR-21: Minimum number of responsibilities required in a job description
`job_description_min_qualifications`	integer	no	1	HR-21: Minimum number of qualifications required in a job description
`job_description_pdf_template_id`	uuid	yes		HR-21: PF-64 letterhead ID override for job description PDFs. NULL = use org default
`fingerprint_clearance_alert_days`	integer[]	yes	ARRAY[90, 60, 30]	HR-28: day thresholds for expiration alerts; sorted desc in job; max 5 positive integers
`fingerprint_clearance_onboarding_required`	boolean	no	true	HR-28: when false, HR-03 onboarding clearance gate is skipped
`internal_mobility_eligibility_scope`	text	no	’site’::text
`internal_mobility_manager_notify_stage`	text	no	’shortlisted’::text
`internal_mobility_min_tenure_days`	integer	no	90
`internal_mobility_score_ranking_enabled`	boolean	no	false
`internal_mobility_require_pay_approval_before_transfer`	boolean	no	true
`internal_mobility_record_retention_years`	integer	no	2
`require_position_for_posting`	boolean	yes	false	When TRUE, HR-09 job postings require a linked approved/vacant position
`position_filled_threshold`	integer	yes	80	Assignment percentage threshold (50-100) for auto-transitioning position to filled
`auto_vacancy_on_termination`	boolean	yes	true	Auto-vacate position when assigned employee is terminated
`position_code_auto_generate`	boolean	yes	true	Auto-generate position codes on creation
`position_code_prefix`	text	yes	’POS’::text	Prefix for auto-generated position codes (max 10 chars)
`handbook_default_acknowledgment_due_days`	integer	no	14
`sms_phi_detection_mode`	text	no	’warn’::text	HR-09-P5: How bulk SMS composer reacts to PHI in message body (warn\|block\|off).
`candidate_portal_session_hours`	integer	no	24	HR-09-P5: Magic-link session lifetime for candidate self-service portal.
`reference_min_required`	integer	no	3	HR-09-P5: Minimum completed references required before an offer can be sent.
`sms_enabled`	boolean	no	false	HR-09-P5: Master switch for SMS candidate communications (TCPA gated).
`sms_business_hours_start`	time without time zone	no	’09:00:00’::time without time zone	HR-09-P5: TCPA-compliant earliest SMS send time (org local).
`sms_business_hours_end`	time without time zone	no	’20:00:00’::time without time zone	HR-09-P5: TCPA-compliant latest SMS send time (org local).
`pathway_templates_enabled`	boolean	no	false	HR-13-EN-08: Enables qualification pathway templates in HR-13 UI for an organization.
`eeo1_filing_enabled`	boolean	no	false	HR-42: gate EEO-1 reporting features for the org.
`eeo1_headcount_threshold`	integer	no	100
`federal_contractor_mode`	boolean	no	false
`eeo1_min_completeness_percent`	numeric(5,2)	no	95	HR-42: EEO completeness threshold below which export warns/blocks (0–100).
`report_artifact_retention_days`	integer	no	2555	HR-42: retention for report artifacts/audit (default 2555 ≈ 7y for EEO-1).
`benefits_census_default_template`	text	no	’v1_flat_csv’::text	HR-42: default carrier census template; export remains gated on Privacy Officer sign-off.

Foreign keys:

bgc_default_provider_id → hr_background_check_providers(id)
created_by → pf_profiles(id)
default_onboarding_template_id → hr_onboarding_templates(id)
organization_id → pf_organizations(id)
updated_by → pf_profiles(id)

Policies:

hr_module_settings_delete (DELETE)
hr_module_settings_insert (INSERT)
hr_module_settings_update (UPDATE)
users_view_org_settings (SELECT)

`hr_offboarding_checklist_items`

RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default	Comment
`id`	uuid	no	gen_random_uuid()
`offboarding_instance_id`	uuid	no
`title`	text	no
`category`	offboarding_category	no
`assigned_to`	uuid	yes
`completed_at`	timestamp with time zone	yes
`completed_by`	uuid	yes
`notes`	text	yes
`display_order`	integer	no	0
`created_at`	timestamp with time zone	no	now()
`organization_id`	uuid	no
`custom_fields`	jsonb	no	’{}‘::jsonb	Organization-specific metadata (e.g., {“equipment_serial”: “LAPTOP-001”, “ad_account”: “jsmith@org.com”, “parking_pass”: “P-101”})
`updated_at`	timestamp with time zone	yes	now()

Foreign keys:

assigned_to → pf_profiles(id)
completed_by → pf_profiles(id)
offboarding_instance_id → hr_offboarding_instances(id)
organization_id → pf_organizations(id)

Policies:

Assigned users and HR admins can update checklist items (UPDATE)
Users can view accessible offboarding checklist items (SELECT)
hr_offboarding_checklist_items_delete (DELETE)
hr_offboarding_checklist_items_insert (INSERT)

`hr_offboarding_instances`

RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default	Comment
`id`	uuid	no	gen_random_uuid()
`employee_id`	uuid	no
`organization_id`	uuid	no
`termination_date`	date	no
`termination_type`	termination_type	no
`reason`	text	yes
`exit_interview_completed`	boolean	yes	false
`exit_interview_notes`	text	yes
`status`	onboarding_status	no	’in_progress’::onboarding_status
`completed_at`	timestamp with time zone	yes
`created_at`	timestamp with time zone	no	now()
`created_by`	uuid	no
`updated_at`	timestamp with time zone	no	now()
`custom_fields`	jsonb	no	’{}‘::jsonb	Exit-specific metadata (e.g., {“exit_score”: 8, “rehire_eligible”: true, “knowledge_transfer_complete”: true, “final_paycheck_date”: “2025-12-15”, “cobra_notified”: true})
`updated_by`	uuid	yes		User who last updated this record

Foreign keys:

created_by → pf_profiles(id)
employee_id → hr_employees(id)
organization_id → pf_organizations(id)
updated_by → users(id)

Policies:

Users can view accessible offboarding instances (SELECT)
hr_offboarding_instances_delete (DELETE)
hr_offboarding_instances_insert (INSERT)
hr_offboarding_instances_update (UPDATE)

`hr_offer_approvals`

RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`offer_id`	uuid	no
`approval_level`	integer	no	1
`approver_role`	text	no
`approver_id`	uuid	yes
`status`	text	no	’pending’::text
`notes`	text	yes
`responded_at`	timestamp with time zone	yes
`created_at`	timestamp with time zone	no	now()
`updated_at`	timestamp with time zone	no	now()
`custom_fields`	jsonb	no	’{}‘::jsonb

Foreign keys:

approver_id → pf_profiles(id)
offer_id → hr_offers(id)
organization_id → pf_organizations(id)

Policies:

hr_offer_approvals_delete (DELETE)
hr_offer_approvals_insert (INSERT)
hr_offer_approvals_select (SELECT)
hr_offer_approvals_update (UPDATE)

`hr_offer_counteroffers`

RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default	Comment
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`offer_id`	uuid	no
`round_number`	integer	no	1
`initiator`	text	no
`proposed_salary`	numeric(12,2)	yes
`proposed_start_date`	date	yes
`proposed_bonus`	numeric(12,2)	yes
`proposed_equity`	text	yes
`proposed_benefits`	text	yes
`additional_requests`	text	yes
`status`	text	no	’pending’::text
`response_notes`	text	yes
`responded_at`	timestamp with time zone	yes
`responded_by`	uuid	yes
`created_at`	timestamp with time zone	no	now()
`updated_at`	timestamp with time zone	no	now()
`created_by`	uuid	yes
`custom_fields`	jsonb	no	’{}‘::jsonb	Organization-specific metadata for offer counteroffers

Foreign keys:

created_by → pf_profiles(id)
offer_id → hr_offers(id)
organization_id → pf_organizations(id)
responded_by → pf_profiles(id)

Policies:

hr_offer_counteroffers_delete (DELETE)
hr_offer_counteroffers_insert (INSERT)
hr_offer_counteroffers_select (SELECT)
hr_offer_counteroffers_update (UPDATE)

`hr_offer_templates`

RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`name`	text	no
`description`	text	yes
`position_id`	uuid	yes
`department_id`	uuid	yes
`employment_type`	text	no	’full_time’::text
`default_salary_min`	numeric(12,2)	yes
`default_salary_max`	numeric(12,2)	yes
`default_bonus_amount`	numeric(12,2)	yes
`default_equity_amount`	text	yes
`default_benefits_summary`	text	yes
`offer_letter_template`	text	yes
`additional_terms`	text	yes
`validity_days`	integer	no	7
`requires_approval`	boolean	no	false
`is_active`	boolean	no	true
`custom_fields`	jsonb	no	’{}‘::jsonb
`created_at`	timestamp with time zone	no	now()
`updated_at`	timestamp with time zone	no	now()
`created_by`	uuid	yes
`updated_by`	uuid	yes

Foreign keys:

created_by → pf_profiles(id)
department_id → pf_departments(id)
organization_id → pf_organizations(id)
position_id → hr_positions(id)
updated_by → pf_profiles(id)

Policies:

hr_offer_templates_delete (DELETE)
hr_offer_templates_insert (INSERT)
hr_offer_templates_select (SELECT)
hr_offer_templates_update (UPDATE)

`hr_offers`

HR-09: Job offers with approval workflow RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default	Comment
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`application_id`	uuid	yes
`position_id`	uuid	yes
`department_id`	uuid	yes
`site_id`	uuid	yes
`offer_status`	hr_offer_status	no	’draft’::hr_offer_status
`salary_amount`	numeric(12,2)	no
`salary_currency`	text	yes	’USD’::text
`salary_period`	text	yes	’annual’::text
`employment_type`	text	no	’full_time’::text
`start_date`	date	yes
`benefits_summary`	text	yes
`additional_terms`	text	yes
`offer_letter_url`	text	yes
`requires_approval`	boolean	yes	true
`approved_by`	uuid	yes
`approved_at`	timestamp with time zone	yes
`approval_notes`	text	yes
`sent_at`	timestamp with time zone	yes
`sent_by`	uuid	yes
`expires_at`	timestamp with time zone	yes
`responded_at`	timestamp with time zone	yes
`response_notes`	text	yes
`custom_fields`	jsonb	no	’{}‘::jsonb
`created_at`	timestamp with time zone	no	now()
`updated_at`	timestamp with time zone	no	now()
`created_by`	uuid	yes
`updated_by`	uuid	yes
`signature_status`	text	yes	’pending’::text
`signature_requested_at`	timestamp with time zone	yes
`signature_completed_at`	timestamp with time zone	yes
`signature_ip_address`	text	yes
`signed_document_url`	text	yes
`candidate_id`	uuid	yes		Optional direct-recipient candidate reference for offers created without hr_applications.
`employee_id`	uuid	yes		Optional direct-recipient employee reference for internal promotion/transfer offers.

Foreign keys:

application_id → hr_applications(id)
approved_by → pf_profiles(id)
candidate_id → hr_candidates(id)
created_by → pf_profiles(id)
department_id → pf_departments(id)
employee_id → hr_employees(id)
organization_id → pf_organizations(id)
position_id → hr_positions(id)
sent_by → pf_profiles(id)
site_id → pf_sites(id)
updated_by → pf_profiles(id)

Policies:

hr_offers_delete (DELETE)
hr_offers_insert (INSERT)
hr_offers_select (SELECT)
hr_offers_update (UPDATE)

`hr_onboarding_instances`

RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default	Comment
`id`	uuid	no	gen_random_uuid()
`employee_id`	uuid	no
`organization_id`	uuid	no
`template_id`	uuid	yes
`start_date`	date	no
`status`	onboarding_status	no	’not_started’::onboarding_status
`progress_percent`	integer	no	0
`completed_at`	timestamp with time zone	yes
`created_at`	timestamp with time zone	no	now()
`created_by`	uuid	no
`updated_at`	timestamp with time zone	no	now()
`custom_fields`	jsonb	no	’{}‘::jsonb	Onboarding-specific details (e.g., {“mentor_id”: “emp-123”, “buddy_id”: “emp-456”, “special_accommodations”: [“remote”], “equipment_needed”: [“laptop”, “badge”], “workspace_assigned”: “Floor 2, Desk 12”})
`updated_by`	uuid	yes		User who last updated this record

Foreign keys:

created_by → pf_profiles(id)
employee_id → hr_employees(id)
organization_id → pf_organizations(id)
template_id → hr_onboarding_templates(id)
updated_by → users(id)

Policies:

HR admins can create onboarding instances (INSERT)
HR admins can delete onboarding instances (DELETE)
HR admins can update onboarding instances (UPDATE)
Users can view accessible onboarding instances (SELECT)

`hr_onboarding_task_instances`

RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default	Comment
`id`	uuid	no	gen_random_uuid()
`onboarding_instance_id`	uuid	no
`task_id`	uuid	yes
`title`	text	no
`description`	text	yes
`category`	task_category	no
`assigned_to`	uuid	yes
`due_date`	date	yes
`status`	task_status	no	’pending’::task_status
`completed_at`	timestamp with time zone	yes
`completed_by`	uuid	yes
`notes`	text	yes
`form_submission_id`	uuid	yes
`document_id`	uuid	yes
`credential_id`	uuid	yes
`created_at`	timestamp with time zone	no	now()
`updated_at`	timestamp with time zone	no	now()
`organization_id`	uuid	no
`custom_fields`	jsonb	no	’{}‘::jsonb	Organization-specific metadata (e.g., {“reviewer_notes”: “Verified ID”, “external_reference”: “BG-CHECK-123”})

Foreign keys:

assigned_to → pf_profiles(id)
completed_by → pf_profiles(id)
credential_id → hr_employee_credentials(id)
document_id → pf_documents(id)
form_submission_id → fw_form_submissions(id)
onboarding_instance_id → hr_onboarding_instances(id)
organization_id → pf_organizations(id)
task_id → hr_onboarding_tasks(id)

Policies:

Assigned users can update their tasks (UPDATE)
Users can view accessible task instances (SELECT)
hr_onboarding_task_instances_delete (DELETE)
hr_onboarding_task_instances_insert (INSERT)

`hr_onboarding_tasks`

RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default	Comment
`id`	uuid	no	gen_random_uuid()
`template_id`	uuid	no
`title`	text	no
`description`	text	yes
`category`	task_category	no
`assignee_role`	assignee_role	yes
`due_days_offset`	integer	no	0
`is_required`	boolean	no	true
`form_id`	uuid	yes
`credential_type_id`	uuid	yes
`requires_document`	boolean	yes	false
`display_order`	integer	no	0
`created_at`	timestamp with time zone	no	now()
`organization_id`	uuid	no
`custom_fields`	jsonb	no	’{}‘::jsonb	Organization-specific metadata (e.g., {“compliance_category”: “HIPAA”, “external_system_task_id”: “TASK-001”})
`updated_at`	timestamp with time zone	yes	now()

Foreign keys:

credential_type_id → hr_credential_types(id)
form_id → fw_forms(id)
organization_id → pf_organizations(id)
template_id → hr_onboarding_templates(id)

Policies:

Users can view template tasks (SELECT)
hr_onboarding_tasks_delete (DELETE)
hr_onboarding_tasks_insert (INSERT)
hr_onboarding_tasks_update (UPDATE)

`hr_onboarding_tasks_audit`

Audit trail for onboarding tasks removed by corrective migrations. RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default
`id`	uuid	no
`organization_id`	uuid	yes
`template_id`	uuid	yes
`title`	text	yes
`deleted_at`	timestamp with time zone	no	now()
`deleted_by`	text	no	’migration’::text
`reason`	text	no	’corrective_dedupe’::text
`original_created_at`	timestamp with time zone	yes

Policies:

hr_onboarding_tasks_audit_delete_disabled (DELETE)
hr_onboarding_tasks_audit_insert (INSERT)
hr_onboarding_tasks_audit_select (SELECT)
hr_onboarding_tasks_audit_update_disabled (UPDATE)

`hr_onboarding_templates`

RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default	Comment
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`name`	text	no
`description`	text	yes
`position_id`	uuid	yes
`department_id`	uuid	yes
`is_clinical`	boolean	no	false
`is_active`	boolean	no	true
`created_at`	timestamp with time zone	no	now()
`created_by`	uuid	no
`updated_at`	timestamp with time zone	no	now()
`updated_by`	uuid	yes
`custom_fields`	jsonb	no	’{}‘::jsonb	Template-specific metadata (e.g., {“state_requirements”: [“CA”], “certification_level”: “advanced”, “estimated_hours”: 40, “department_specific”: true, “completion_bonus”: 500})

Foreign keys:

created_by → pf_profiles(id)
department_id → pf_departments(id)
organization_id → pf_organizations(id)
position_id → hr_positions(id)
updated_by → pf_profiles(id)

Policies:

Users can view org templates (SELECT)
hr_onboarding_templates_delete (DELETE)
hr_onboarding_templates_insert (INSERT)
hr_onboarding_templates_update (UPDATE)

`hr_pay_adjustments`

RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`site_id`	uuid	yes
`employee_id`	uuid	no
`adjustment_type`	text	no
`description`	text	no
`amount`	numeric(10,2)	no
`pay_period_start`	date	no
`pay_period_end`	date	no
`is_recurring`	boolean	no	false
`status`	text	no	’pending’::text
`processed_at`	timestamp with time zone	yes
`processed_by`	uuid	yes
`custom_fields`	jsonb	no	’{}‘::jsonb
`created_at`	timestamp with time zone	no	now()
`created_by`	uuid	no
`updated_at`	timestamp with time zone	no	now()
`updated_by`	uuid	yes

Foreign keys:

created_by → pf_profiles(id)
employee_id → hr_employees(id)
organization_id → pf_organizations(id)
processed_by → pf_profiles(id)
site_id → pf_sites(id)
updated_by → pf_profiles(id)

Policies:

hr_pay_adjustments_delete (DELETE)
hr_pay_adjustments_insert (INSERT)
hr_pay_adjustments_select (SELECT)
hr_pay_adjustments_update (UPDATE)

`hr_pay_calendar_events`

RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`pay_schedule_id`	uuid	no
`period_start`	date	no
`period_end`	date	no
`pay_date`	date	no
`submission_deadline`	date	no
`approval_deadline`	date	no
`processing_deadline`	date	no
`payroll_run_id`	uuid	yes
`status`	text	no	’scheduled’::text
`created_at`	timestamp with time zone	no	now()
`updated_at`	timestamp with time zone	no	now()
`created_by`	uuid	yes
`updated_by`	uuid	yes

Foreign keys:

created_by → pf_profiles(id)
organization_id → pf_organizations(id)
pay_schedule_id → hr_pay_schedules(id)
payroll_run_id → hr_payroll_runs(id)
updated_by → pf_profiles(id)

Policies:

hr_pay_calendar_events_delete (DELETE)
hr_pay_calendar_events_insert (INSERT)
hr_pay_calendar_events_select (SELECT)
hr_pay_calendar_events_update (UPDATE)

`hr_pay_rates`

RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`site_id`	uuid	yes
`employee_id`	uuid	no
`effective_date`	date	no
`end_date`	date	yes
`rate_type`	text	no
`base_rate`	numeric(10,2)	no
`currency`	text	no	’USD’::text
`pay_frequency`	text	no	’bi_weekly’::text
`overtime_eligible`	boolean	no	true
`overtime_multiplier`	numeric(3,2)	no	1.50
`custom_fields`	jsonb	no	’{}‘::jsonb
`created_at`	timestamp with time zone	no	now()
`created_by`	uuid	no
`updated_at`	timestamp with time zone	no	now()
`updated_by`	uuid	yes

Foreign keys:

created_by → pf_profiles(id)
employee_id → hr_employees(id)
organization_id → pf_organizations(id)
site_id → pf_sites(id)
updated_by → pf_profiles(id)

Policies:

hr_pay_rates_delete (DELETE)
hr_pay_rates_insert (INSERT)
hr_pay_rates_select (SELECT)
hr_pay_rates_update (UPDATE)

`hr_pay_schedules`

RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`name`	text	no	’Default’::text
`frequency`	text	no
`anchor_date`	date	no
`semimonthly_first_day`	integer	yes
`semimonthly_second_day`	integer	yes
`pay_date_offset_days`	integer	no	5
`adjust_for_holidays`	boolean	yes	true
`adjust_direction`	text	yes	’before’::text
`timesheet_submission_deadline_days`	integer	yes	5
`timesheet_approval_deadline_days`	integer	yes	3
`payroll_processing_deadline_days`	integer	yes	2
`auto_create_draft`	boolean	yes	false
`auto_create_days_before`	integer	yes	7
`reminder_days_before_submission`	integer	yes	3
`reminder_days_before_approval`	integer	yes	1
`is_active`	boolean	no	true
`custom_fields`	jsonb	no	’{}‘::jsonb
`created_at`	timestamp with time zone	no	now()
`updated_at`	timestamp with time zone	no	now()
`created_by`	uuid	yes
`updated_by`	uuid	yes

Foreign keys:

created_by → pf_profiles(id)
organization_id → pf_organizations(id)
updated_by → pf_profiles(id)

Policies:

hr_pay_schedules_delete (DELETE)
hr_pay_schedules_insert (INSERT)
hr_pay_schedules_select (SELECT)
hr_pay_schedules_update (UPDATE)

`hr_pay_stubs`

HR-PAY-03: Employee pay stubs for self-service portal RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default	Comment
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`employee_id`	uuid	no
`payroll_run_id`	uuid	no
`payroll_line_item_id`	uuid	yes
`period_start`	date	no
`period_end`	date	no
`pay_date`	date	no
`stub_data`	jsonb	no	’{}‘::jsonb	Denormalized pay stub data for historical accuracy (earnings, deductions, taxes, YTD)
`pdf_file_path`	text	yes
`pdf_generated_at`	timestamp with time zone	yes
`custom_fields`	jsonb	no	’{}‘::jsonb
`created_at`	timestamp with time zone	no	now()
`updated_at`	timestamp with time zone	no	now()
`created_by`	uuid	yes
`updated_by`	uuid	yes

Foreign keys:

created_by → users(id)
employee_id → hr_employees(id)
organization_id → pf_organizations(id)
payroll_line_item_id → hr_payroll_line_items(id)
payroll_run_id → hr_payroll_runs(id)
updated_by → users(id)

Policies:

hr_pay_stubs_delete (DELETE)
hr_pay_stubs_insert (INSERT)
hr_pay_stubs_select (SELECT)
hr_pay_stubs_update (UPDATE)

`hr_payment_batches`

HR-PAY-03: Payment batches for direct deposit processing RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default	Comment
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`payroll_run_id`	uuid	no
`batch_number`	text	no
`payment_method`	text	no		Method: plaid_transfer (Plaid API), nacha_file (NACHA export), manual (manual processing)
`status`	text	no	’pending’::text
`total_amount`	numeric(14,2)	no	0
`payment_count`	integer	no	0
`successful_count`	integer	no	0
`failed_count`	integer	no	0
`plaid_batch_id`	text	yes
`nacha_file_path`	text	yes
`submitted_at`	timestamp with time zone	yes
`submitted_by`	uuid	yes
`completed_at`	timestamp with time zone	yes
`error_message`	text	yes
`custom_fields`	jsonb	no	’{}‘::jsonb
`created_at`	timestamp with time zone	no	now()
`updated_at`	timestamp with time zone	no	now()
`created_by`	uuid	yes
`updated_by`	uuid	yes

Foreign keys:

created_by → users(id)
organization_id → pf_organizations(id)
payroll_run_id → hr_payroll_runs(id)
submitted_by → users(id)
updated_by → users(id)

Policies:

hr_payment_batches_delete (DELETE)
hr_payment_batches_insert (INSERT)
hr_payment_batches_select (SELECT)
hr_payment_batches_update (UPDATE)

`hr_payment_checks`

HR-PAY-03 Phase 2: Physical check records linked to payment items RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default	Comment
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`payment_batch_id`	uuid	no
`payment_item_id`	uuid	no
`check_number`	text	no
`print_status`	text	no	’pending’::text
`vendor_reference`	text	yes
`amount`	numeric(10,2)	no
`payee_name`	text	no
`pay_date`	date	no
`custom_fields`	jsonb	no	’{}‘::jsonb	Org-specific check metadata. Do not store PII or sensitive payment credentials.
`created_at`	timestamp with time zone	no	now()
`updated_at`	timestamp with time zone	no	now()
`created_by`	uuid	yes
`updated_by`	uuid	yes

Foreign keys:

created_by → users(id)
organization_id → pf_organizations(id)
payment_batch_id → hr_payment_batches(id)
payment_item_id → hr_payment_items(id)
updated_by → users(id)

Policies:

hr_payment_checks_delete (DELETE)
hr_payment_checks_insert (INSERT)
hr_payment_checks_select (SELECT)
hr_payment_checks_update (UPDATE)

`hr_payment_items`

HR-PAY-03: Individual payment items within a batch RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default	Comment
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`payment_batch_id`	uuid	no
`employee_id`	uuid	no
`bank_account_id`	uuid	yes
`amount`	numeric(12,2)	no
`status`	text	no	’pending’::text
`plaid_transfer_id`	text	yes
`return_code`	text	yes		ACH return code (e.g., R01=Insufficient Funds, R02=Account Closed)
`return_reason`	text	yes
`returned_at`	timestamp with time zone	yes
`settled_at`	timestamp with time zone	yes
`error_message`	text	yes
`custom_fields`	jsonb	no	’{}‘::jsonb
`created_at`	timestamp with time zone	no	now()
`updated_at`	timestamp with time zone	no	now()
`created_by`	uuid	yes
`updated_by`	uuid	yes
`payment_method`	text	no	’direct_deposit’::text	direct_deposit = ACH via bank_account_id; check = physical check, see hr_payment_checks

Foreign keys:

bank_account_id → hr_employee_bank_accounts(id)
created_by → users(id)
employee_id → hr_employees(id)
organization_id → pf_organizations(id)
payment_batch_id → hr_payment_batches(id)
updated_by → users(id)

Policies:

hr_payment_items_delete (DELETE)
hr_payment_items_insert (INSERT)
hr_payment_items_select (SELECT)
hr_payment_items_update (UPDATE)

`hr_payroll_audit_log`

Audit log for payroll actions - HR-07 Phase 5 RLS: ✅ enabled (2 policies)

Column	Type	Nullable	Default
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`payroll_run_id`	uuid	yes
`employee_id`	uuid	yes
`action_type`	text	no
`action_description`	text	no
`previous_value`	jsonb	yes
`new_value`	jsonb	yes
`performed_by`	uuid	yes
`performed_at`	timestamp with time zone	no	now()
`ip_address`	text	yes
`user_agent`	text	yes
`custom_fields`	jsonb	no	’{}‘::jsonb
`created_at`	timestamp with time zone	yes	now()
`updated_at`	timestamp with time zone	yes	now()

Foreign keys:

employee_id → hr_employees(id)
organization_id → pf_organizations(id)
payroll_run_id → hr_payroll_runs(id)
performed_by → pf_profiles(id)

Policies:

hr_payroll_audit_log_insert (INSERT)
hr_payroll_audit_log_select (SELECT)

`hr_payroll_deduction_items`

HR-PAY-02: Append-only per-payroll-run deduction line items for audit trail RLS: ✅ enabled (2 policies)

Column	Type	Nullable	Default
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`payroll_run_id`	uuid	no
`payroll_line_item_id`	uuid	no
`employee_id`	uuid	no
`employee_deduction_id`	uuid	no
`deduction_name`	text	no
`category`	text	no
`amount`	numeric(10,2)	no
`ytd_after`	numeric(12,2)	no
`created_at`	timestamp with time zone	no	now()

Foreign keys:

employee_deduction_id → hr_employee_deductions(id)
employee_id → hr_employees(id)
organization_id → pf_organizations(id)
payroll_line_item_id → hr_payroll_line_items(id)
payroll_run_id → hr_payroll_runs(id)

Policies:

hr_payroll_deduction_items_insert (INSERT)
hr_payroll_deduction_items_select (SELECT)

`hr_payroll_exports`

RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default	Comment
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`payroll_run_id`	uuid	no
`export_format`	text	no
`file_name`	text	no
`file_size_bytes`	integer	yes
`record_count`	integer	no	0
`created_at`	timestamp with time zone	no	now()
`created_by`	uuid	yes
`custom_fields`	jsonb	no	’{}‘::jsonb
`updated_by`	uuid	yes		User who last updated this record
`updated_at`	timestamp with time zone	yes	now()

Foreign keys:

created_by → pf_profiles(id)
organization_id → pf_organizations(id)
payroll_run_id → hr_payroll_runs(id)
updated_by → users(id)

Policies:

hr_payroll_exports_delete (DELETE)
hr_payroll_exports_insert (INSERT)
hr_payroll_exports_select (SELECT)
hr_payroll_exports_update (UPDATE)

`hr_payroll_line_items`

RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`payroll_run_id`	uuid	no
`employee_id`	uuid	no
`timesheet_id`	uuid	yes
`regular_hours`	numeric(6,2)	no	0
`overtime_hours`	numeric(6,2)	no	0
`pto_hours`	numeric(6,2)	no	0
`other_hours`	numeric(6,2)	no	0
`base_rate`	numeric(10,2)	no
`overtime_rate`	numeric(10,2)	no
`regular_pay`	numeric(10,2)	no	0
`overtime_pay`	numeric(10,2)	no	0
`pto_pay`	numeric(10,2)	no	0
`adjustments_total`	numeric(10,2)	no	0
`gross_pay`	numeric(10,2)	no	0
`adjustments`	jsonb	no	’[]‘::jsonb
`calculated_at`	timestamp with time zone	no	now()
`custom_fields`	jsonb	no	’{}‘::jsonb
`created_at`	timestamp with time zone	no	now()
`created_by`	uuid	no
`updated_at`	timestamp with time zone	no	now()
`updated_by`	uuid	yes
`federal_tax_withheld`	numeric(10,2)	yes	0
`state_tax_withheld`	numeric(10,2)	yes	0
`ss_tax_withheld`	numeric(10,2)	yes	0
`medicare_tax_withheld`	numeric(10,2)	yes	0
`additional_medicare_withheld`	numeric(10,2)	yes	0
`pre_tax_deductions`	numeric(10,2)	yes	0
`post_tax_deductions`	numeric(10,2)	yes	0
`net_pay`	numeric(10,2)	yes
`ytd_gross`	numeric(12,2)	yes	0
`ytd_federal_tax`	numeric(12,2)	yes	0
`ytd_state_tax`	numeric(12,2)	yes	0
`ytd_ss_tax`	numeric(12,2)	yes	0
`ytd_medicare_tax`	numeric(12,2)	yes	0

Foreign keys:

created_by → pf_profiles(id)
employee_id → hr_employees(id)
organization_id → pf_organizations(id)
payroll_run_id → hr_payroll_runs(id)
timesheet_id → hr_timesheets(id)
updated_by → pf_profiles(id)

Policies:

hr_payroll_line_items_delete (DELETE)
hr_payroll_line_items_insert (INSERT)
hr_payroll_line_items_select (SELECT)
hr_payroll_line_items_update (UPDATE)

`hr_payroll_runs`

RLS: ✅ enabled (3 policies)

Column	Type	Nullable	Default
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`site_id`	uuid	yes
`run_name`	text	no
`period_start`	date	no
`period_end`	date	no
`pay_date`	date	no
`status`	text	no	’draft’::text
`total_gross_pay`	numeric(12,2)	no	0
`total_net_pay`	numeric(12,2)	no	0
`total_employees`	integer	no	0
`exported_at`	timestamp with time zone	yes
`exported_by`	uuid	yes
`export_format`	text	yes
`export_file_path`	text	yes
`custom_fields`	jsonb	no	’{}‘::jsonb
`created_at`	timestamp with time zone	no	now()
`created_by`	uuid	no
`updated_at`	timestamp with time zone	no	now()
`updated_by`	uuid	yes
`finalized_at`	timestamp with time zone	yes
`finalized_by`	uuid	yes
`total_federal_tax`	numeric(12,2)	yes
`total_state_tax`	numeric(12,2)	yes
`total_ss_tax`	numeric(12,2)	yes
`total_medicare_tax`	numeric(12,2)	yes
`total_deductions`	numeric(12,2)	yes

Foreign keys:

created_by → pf_profiles(id)
exported_by → pf_profiles(id)
finalized_by → pf_profiles(id)
organization_id → pf_organizations(id)
site_id → pf_sites(id)
updated_by → pf_profiles(id)

Policies:

hr_payroll_runs_insert (INSERT)
hr_payroll_runs_select (SELECT)
hr_payroll_runs_update (UPDATE)

`hr_performance_competencies`

Competency assessments within performance reviews RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default	Comment
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`review_id`	uuid	no
`credential_type_id`	uuid	yes
`competency_name`	text	no
`competency_category`	text	yes
`rating`	text	no	’not_assessed’::text	Rating: exceeds_expectations, meets_expectations, below_expectations, not_assessed
`comments`	text	yes
`has_gap`	boolean	yes	false
`gap_description`	text	yes
`training_recommendation_id`	uuid	yes		Future FK to GR-02 training
`custom_fields`	jsonb	no	’{}‘::jsonb	Organization-specific metadata
`created_at`	timestamp with time zone	no	now()
`updated_at`	timestamp with time zone	no	now()
`created_by`	uuid	yes
`updated_by`	uuid	yes

Foreign keys:

created_by → pf_profiles(id)
credential_type_id → hr_credential_types(id)
organization_id → pf_organizations(id)
review_id → hr_performance_reviews(id)
updated_by → pf_profiles(id)

Policies:

hr_performance_competencies_delete (DELETE)
hr_performance_competencies_insert (INSERT)
hr_performance_competencies_select (SELECT)
hr_performance_competencies_update (UPDATE)

`hr_performance_cycles`

Performance review cycles for scheduling and managing reviews RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default	Comment
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`name`	text	no
`cycle_type`	text	no	’annual’::text	Type: annual, quarterly, project_based, ad_hoc
`start_date`	date	no
`end_date`	date	no
`review_deadline`	date	no
`review_template_id`	uuid	yes
`auto_assign_enabled`	boolean	yes	true
`auto_assign_rules`	jsonb	yes	’{}‘::jsonb
`status`	text	yes	’draft’::text	Status: draft, active, completed, archived
`description`	text	yes
`custom_fields`	jsonb	no	’{}‘::jsonb	Organization-specific metadata
`created_at`	timestamp with time zone	no	now()
`updated_at`	timestamp with time zone	no	now()
`created_by`	uuid	yes
`updated_by`	uuid	yes
`site_id`	uuid	yes		Optional site scope for multi-site organizations
`deleted_at`	timestamp with time zone	yes		Soft delete timestamp - null means active

Foreign keys:

created_by → pf_profiles(id)
organization_id → pf_organizations(id)
site_id → pf_sites(id)
updated_by → pf_profiles(id)

Policies:

hr_performance_cycles_delete (DELETE)
hr_performance_cycles_insert (INSERT)
hr_performance_cycles_select (SELECT)
hr_performance_cycles_update (UPDATE)

`hr_performance_feedback`

360 feedback from peers, managers, direct reports RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default	Comment
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`employee_id`	uuid	no
`feedback_source_id`	uuid	no
`review_id`	uuid	yes
`feedback_type`	text	no	’peer’::text	Type: peer, direct_report, manager, self, other
`is_anonymous`	boolean	yes	false
`feedback_text`	text	yes
`strengths`	text	yes
`areas_for_improvement`	text	yes
`overall_rating`	text	yes
`status`	text	yes	’pending’::text	Status: pending, completed, declined
`completed_at`	timestamp with time zone	yes
`requested_by`	uuid	yes
`requested_at`	timestamp with time zone	no	now()
`deadline_date`	date	yes
`custom_fields`	jsonb	no	’{}‘::jsonb	Organization-specific metadata
`created_at`	timestamp with time zone	no	now()
`updated_at`	timestamp with time zone	no	now()
`created_by`	uuid	yes		User who created this feedback record
`updated_by`	uuid	yes		User who last updated this feedback record

Foreign keys:

created_by → pf_profiles(id)
employee_id → hr_employees(id)
feedback_source_id → pf_profiles(id)
organization_id → pf_organizations(id)
requested_by → pf_profiles(id)
review_id → hr_performance_reviews(id)
updated_by → pf_profiles(id)

Policies:

hr_performance_feedback_delete (DELETE)
hr_performance_feedback_insert (INSERT)
hr_performance_feedback_select (SELECT)
hr_performance_feedback_update (UPDATE)

`hr_performance_goals`

Individual performance goals linked to reviews RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default	Comment
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`employee_id`	uuid	no
`review_id`	uuid	yes
`organizational_goal_id`	uuid	yes		Future FK to LO-11 strategic goals
`title`	text	no
`description`	text	yes
`target_value`	numeric	yes
`current_value`	numeric	yes
`unit`	text	yes
`start_date`	date	no
`deadline_date`	date	no
`status`	text	yes	’on_track’::text	Status: on_track, at_risk, completed, not_met, cancelled
`progress_percentage`	integer	yes	0
`notes`	text	yes
`custom_fields`	jsonb	no	’{}‘::jsonb	Organization-specific metadata
`created_at`	timestamp with time zone	no	now()
`updated_at`	timestamp with time zone	no	now()
`created_by`	uuid	yes
`updated_by`	uuid	yes

Foreign keys:

created_by → pf_profiles(id)
employee_id → hr_employees(id)
organization_id → pf_organizations(id)
review_id → hr_performance_reviews(id)
updated_by → pf_profiles(id)

Policies:

hr_performance_goals_delete (DELETE)
hr_performance_goals_insert (INSERT)
hr_performance_goals_select (SELECT)
hr_performance_goals_update (UPDATE)

`hr_performance_improvement_plans`

Performance improvement plans for underperforming employees RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default	Comment
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`employee_id`	uuid	no
`review_id`	uuid	yes
`owner_id`	uuid	no
`title`	text	no
`description`	text	no
`improvement_objectives`	text	no
`start_date`	date	no
`end_date`	date	no
`review_date`	date	yes
`status`	text	yes	’active’::text	Status: active, completed, terminated, cancelled
`outcome`	text	yes		Outcome: successful, unsuccessful, partial
`milestones`	jsonb	yes	’[]‘::jsonb
`notes`	text	yes
`termination_reason`	text	yes
`custom_fields`	jsonb	no	’{}‘::jsonb	Organization-specific metadata
`created_at`	timestamp with time zone	no	now()
`updated_at`	timestamp with time zone	no	now()
`created_by`	uuid	yes
`updated_by`	uuid	yes
`final_assessment`	text	yes
`actual_end_date`	date	yes

Foreign keys:

created_by → pf_profiles(id)
employee_id → hr_employees(id)
organization_id → pf_organizations(id)
owner_id → pf_profiles(id)
review_id → hr_performance_reviews(id)
updated_by → pf_profiles(id)

Policies:

hr_pip_delete (DELETE)
hr_pip_insert (INSERT)
hr_pip_select (SELECT)
hr_pip_update (UPDATE)

`hr_performance_reviews`

Individual performance reviews for employees RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default	Comment
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`employee_id`	uuid	no
`cycle_id`	uuid	yes
`reviewer_id`	uuid	no
`review_period_start`	date	no
`review_period_end`	date	no
`review_date`	date	yes
`overall_rating`	text	yes		Rating: exceeds_expectations, meets_expectations, below_expectations, not_assessed
`goals_rating`	text	yes
`competencies_rating`	text	yes
`behavior_rating`	text	yes
`manager_comments`	text	yes
`employee_self_assessment`	text	yes
`employee_acknowledgment_comments`	text	yes
`status`	text	yes	’draft’::text	Status: draft, in_progress, pending_acknowledgment, completed, archived
`employee_acknowledged_at`	timestamp with time zone	yes
`employee_acknowledged_by`	uuid	yes
`notes`	text	yes
`custom_fields`	jsonb	no	’{}‘::jsonb	Organization-specific metadata
`created_at`	timestamp with time zone	no	now()
`updated_at`	timestamp with time zone	no	now()
`created_by`	uuid	yes
`updated_by`	uuid	yes
`goals_comments`	text	yes
`competencies_comments`	text	yes
`behavior_comments`	text	yes
`overall_comments`	text	yes

Foreign keys:

created_by → pf_profiles(id)
cycle_id → hr_performance_cycles(id)
employee_acknowledged_by → pf_profiles(id)
employee_id → hr_employees(id)
organization_id → pf_organizations(id)
reviewer_id → pf_profiles(id)
updated_by → pf_profiles(id)

Policies:

hr_performance_reviews_delete (DELETE)
hr_performance_reviews_insert (INSERT)
hr_performance_reviews_select (SELECT)
hr_performance_reviews_update (UPDATE)

`hr_position_assignments`

RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`position_id`	uuid	no
`employee_id`	uuid	no
`assignment_percentage`	numeric(5,2)	no	100.0
`start_date`	date	no
`end_date`	date	yes
`end_reason`	text	yes
`is_primary`	boolean	no	true
`created_at`	timestamp with time zone	no	now()
`updated_at`	timestamp with time zone	no	now()
`created_by`	uuid	yes
`updated_by`	uuid	yes
`custom_fields`	jsonb	no	’{}‘::jsonb

Foreign keys:

created_by → pf_profiles(id)
employee_id → hr_employees(id)
organization_id → pf_organizations(id)
position_id → hr_positions(id)
updated_by → pf_profiles(id)

Policies:

hr_position_assignments_delete (DELETE)
hr_position_assignments_insert (INSERT)
hr_position_assignments_select (SELECT)
hr_position_assignments_update (UPDATE)

`hr_position_eeo_mappings`

HR-42: maps a position to one of 10 federal EEOC Component 1 job categories (validated app-side per CONTEXT.md D5a). Used in EEO-1 aggregation. RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`position_id`	uuid	no
`eeoc_job_category`	text	no
`custom_fields`	jsonb	no	’{}‘::jsonb
`created_at`	timestamp with time zone	no	now()
`updated_at`	timestamp with time zone	no	now()
`created_by`	uuid	yes
`updated_by`	uuid	yes

Foreign keys:

created_by → pf_profiles(id)
organization_id → pf_organizations(id)
position_id → hr_positions(id)
updated_by → pf_profiles(id)

Policies:

hr_position_eeo_mappings_delete (DELETE)
hr_position_eeo_mappings_insert (INSERT)
hr_position_eeo_mappings_select (SELECT)
hr_position_eeo_mappings_update (UPDATE)

`hr_position_history`

RLS: ✅ enabled (2 policies)

Column	Type	Nullable	Default	Comment
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`position_id`	uuid	no
`changed_at`	timestamp with time zone	no	now()
`changed_by`	uuid	yes
`change_type`	text	no
`previous_state`	jsonb	yes
`new_state`	jsonb	yes
`reason`	text	yes
`corrects_history_id`	uuid	yes		References the original history entry this row corrects (if applicable)
`correction_type`	text	yes		Type of correction: amendment, retraction, or clarification
`correction_reason`	text	yes		Explanation for the correction

Foreign keys:

changed_by → pf_profiles(id)
corrects_history_id → hr_position_history(id)
organization_id → pf_organizations(id)
position_id → hr_positions(id)

Policies:

hr_position_history_insert (INSERT)
hr_position_history_select (SELECT)

`hr_position_skill_requirements`

Required skills for each position RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default	Comment
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`position_id`	uuid	no
`skill_id`	uuid	no
`minimum_proficiency_level`	text	no	’intermediate’::text
`is_mandatory`	boolean	no	true
`priority`	integer	yes	1
`verification_due_days`	integer	yes		Number of days from employee hire date by which this skill must be verified. NULL means no deadline.
`custom_fields`	jsonb	no	’{}‘::jsonb
`created_at`	timestamp with time zone	no	now()
`updated_at`	timestamp with time zone	no	now()
`created_by`	uuid	yes
`updated_by`	uuid	yes
`deleted_at`	timestamp with time zone	yes
`pathway_group`	text	yes		HR-13-EN-08: Propagated pathway group from competency framework skill.
`pathway_logic`	text	no	’required’::text	HR-13-EN-08: Propagated pathway logic from competency framework skill.
`source_framework_id`	uuid	yes		HR-13-EN-08: Source qualification_pathway competency framework used to derive this requirement.

Foreign keys:

created_by → pf_profiles(id)
organization_id → pf_organizations(id)
position_id → hr_positions(id)
skill_id → hr_skills_library(id)
source_framework_id → hr_competency_frameworks(id)
updated_by → pf_profiles(id)

Policies:

hr_position_skill_requirements_delete (DELETE)
hr_position_skill_requirements_insert (INSERT)
hr_position_skill_requirements_select (SELECT)
hr_position_skill_requirements_update (UPDATE)

`hr_positions`

RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default	Comment
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`department_id`	uuid	yes
`title`	text	no
`code`	text	no
`description`	text	yes
`is_clinical`	boolean	no	false
`is_leadership`	boolean	no	false
`is_exempt`	boolean	no	false
`fte_value`	numeric(3,2)	no	1.0
`min_staff_count`	integer	yes	0
`max_staff_count`	integer	yes
`is_active`	boolean	no	true
`created_at`	timestamp with time zone	no	now()
`updated_at`	timestamp with time zone	no	now()
`created_by`	uuid	no
`updated_by`	uuid	yes
`custom_fields`	jsonb	no	’{}‘::jsonb	Organization-specific custom data (e.g., {“certification_required”: [“BLS”, “ACLS”], “on_call_eligible”: true})
`job_description_full`	text	yes		Full structured job description content (duties, qualifications, etc.) for guided form and PDF generation. Existing description column remains as short summary.
`job_description_updated_at`	timestamp with time zone	yes		HR-21: Timestamp of last job_description_full change
`job_description_version`	integer	yes		HR-21: Auto-incremented version number for job description changes. NULL = no description set
`internal_posting_enabled`	boolean	no	false	HR-35: Visible on internal job board when true and position is active.
`status`	text	no	’approved’::text	Position lifecycle status: draft, pending_approval, approved, filled, vacant, frozen, closed
`site_id`	uuid	yes
`reporting_to_position_id`	uuid	yes
`effective_start_date`	date	yes	CURRENT_DATE
`effective_end_date`	date	yes
`budget_reference`	text	yes		FA budget line reference for cost reconciliation
`justification`	text	yes		Business justification for position creation or modification
`approved_by`	uuid	yes
`approved_at`	timestamp with time zone	yes
`deleted_at`	timestamp with time zone	yes

Foreign keys:

approved_by → pf_profiles(id)
created_by → pf_profiles(id)
organization_id → pf_organizations(id)
reporting_to_position_id → hr_positions(id)
site_id → pf_sites(id)
updated_by → pf_profiles(id)

Policies:

HR admins can delete positions (DELETE)
HR admins can insert positions (INSERT)
HR admins can update positions (UPDATE)
Users can view org positions (SELECT)

`hr_proliant_accrual_balances`

HR-44 OQ-6: Read-only mirror of Proliant accrual balances. RLS: ✅ enabled (3 policies)

Column	Type	Nullable	Default
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`employee_id`	uuid	no
`accrual_code`	text	no
`accrual_name`	text	yes
`balance_hours`	numeric(10,2)	no	0
`accrued_ytd_hours`	numeric(10,2)	yes
`used_ytd_hours`	numeric(10,2)	yes
`as_of_date`	date	no
`synced_at`	timestamp with time zone	no	now()
`created_at`	timestamp with time zone	no	now()
`updated_at`	timestamp with time zone	no	now()

Foreign keys:

employee_id → hr_employees(id)
organization_id → pf_organizations(id)

Policies:

hr_proliant_accrual_insert (INSERT)
hr_proliant_accrual_select (SELECT)
hr_proliant_accrual_update (UPDATE)

`hr_proliant_code_mappings`

HR-44 Stage 2: Proliant code → Encore value mapping (earnings, deductions, jobs, shifts, cost centers, taxes, pay frequency/group). RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`integration_id`	uuid	no
`proliant_company_id`	text	no
`code_type`	text	no
`proliant_code`	text	no
`proliant_description`	text	yes
`encore_value`	text	yes
`is_mapped`	boolean	no	false
`custom_fields`	jsonb	no	’{}‘::jsonb
`created_at`	timestamp with time zone	no	now()
`updated_at`	timestamp with time zone	no	now()
`created_by`	uuid	yes
`updated_by`	uuid	yes

Foreign keys:

integration_id → hr_proliant_integrations(id)
organization_id → pf_organizations(id)

Policies:

hr_proliant_codemap_delete (DELETE)
hr_proliant_codemap_insert (INSERT)
hr_proliant_codemap_select (SELECT)
hr_proliant_codemap_update (UPDATE)

`hr_proliant_company_mappings`

HR-44: Multi-EIN Proliant company → org mapping. RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`integration_id`	uuid	no
`proliant_company_id`	text	no
`company_name`	text	yes
`is_primary`	boolean	no	false
`custom_fields`	jsonb	no	’{}‘::jsonb
`created_at`	timestamp with time zone	no	now()
`updated_at`	timestamp with time zone	no	now()
`created_by`	uuid	yes
`updated_by`	uuid	yes

Foreign keys:

integration_id → hr_proliant_integrations(id)
organization_id → pf_organizations(id)

Policies:

hr_proliant_company_delete (DELETE)
hr_proliant_company_insert (INSERT)
hr_proliant_company_select (SELECT)
hr_proliant_company_update (UPDATE)

`hr_proliant_employee_mappings`

HR-44: Proliant ↔ Encore employee mapping. match_hash is HMAC-SHA256(ssn, org_key); never store raw SSN. RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`employee_id`	uuid	no
`proliant_employee_id`	text	no
`proliant_company_id`	text	no
`match_method`	text	no
`match_hash`	text	yes
`last_synced_at`	timestamp with time zone	yes
`sync_status`	text	no	’active’::text
`custom_fields`	jsonb	no	’{}‘::jsonb
`created_at`	timestamp with time zone	no	now()
`updated_at`	timestamp with time zone	no	now()
`created_by`	uuid	yes
`updated_by`	uuid	yes

Foreign keys:

employee_id → hr_employees(id)
organization_id → pf_organizations(id)

Policies:

hr_proliant_emp_delete (DELETE)
hr_proliant_emp_insert (INSERT)
hr_proliant_emp_select (SELECT)
hr_proliant_emp_update (UPDATE)

`hr_proliant_field_mappings`

HR-44: Per-org Proliant→Encore field mapping overrides. RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`integration_id`	uuid	no
`entity_type`	text	no
`proliant_field`	text	no
`encore_field`	text	no
`direction`	text	no	’pull’::text
`is_enabled`	boolean	no	true
`transform_rule`	jsonb	yes
`created_at`	timestamp with time zone	no	now()
`updated_at`	timestamp with time zone	no	now()

Foreign keys:

integration_id → hr_proliant_integrations(id)
organization_id → pf_organizations(id)

Policies:

hr_proliant_fmap_delete (DELETE)
hr_proliant_fmap_insert (INSERT)
hr_proliant_fmap_select (SELECT)
hr_proliant_fmap_update (UPDATE)

`hr_proliant_integrations`

HR-44: One Proliant HCM integration config per org. Credentials via pf_credential_vault. RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default	Comment
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`environment`	text	no	’sandbox’::text
`is_active`	boolean	no	false
`sync_schedule`	text	yes	’0 2 * * *‘::text
`sync_employees`	boolean	no	true
`sync_payroll`	boolean	no	true
`sync_accruals`	boolean	no	true
`push_timesheets`	boolean	no	false
`auto_create_employees`	boolean	no	false
`conflict_strategy`	text	no	’flag’::text
`baa_attested_at`	timestamp with time zone	yes
`baa_attested_by`	uuid	yes
`consecutive_failures`	integer	no	0
`client_id_credential_id`	uuid	yes
`client_secret_credential_id`	uuid	yes
`ssn_hmac_key_credential_id`	uuid	yes
`custom_fields`	jsonb	no	’{}‘::jsonb
`created_at`	timestamp with time zone	no	now()
`updated_at`	timestamp with time zone	no	now()
`created_by`	uuid	yes
`updated_by`	uuid	yes
`push_employees`	boolean	no	false
`sandbox_base_url`	text	yes		Per-org Proliant sandbox CompanyAPI base URL (provided at onboarding). Required before a sandbox integration can be activated. NULL for production-only integrations.

Foreign keys:

client_id_credential_id → pf_credential_vault(id)
client_secret_credential_id → pf_credential_vault(id)
organization_id → pf_organizations(id)
ssn_hmac_key_credential_id → pf_credential_vault(id)

Policies:

hr_proliant_integrations_delete (DELETE)
hr_proliant_integrations_insert (INSERT)
hr_proliant_integrations_select (SELECT)
hr_proliant_integrations_update (UPDATE)

`hr_proliant_payroll_calendars`

HR-44 Stage 2: Proliant payroll calendar periods (one row per pay period per company). RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`integration_id`	uuid	no
`proliant_company_id`	text	no
`calendar_guid`	text	no
`pay_frequency`	text	yes
`period_start`	date	yes
`period_end`	date	yes
`check_date`	date	yes
`is_active`	boolean	no	true
`custom_fields`	jsonb	no	’{}‘::jsonb
`created_at`	timestamp with time zone	no	now()
`updated_at`	timestamp with time zone	no	now()
`created_by`	uuid	yes
`updated_by`	uuid	yes

Foreign keys:

integration_id → hr_proliant_integrations(id)
organization_id → pf_organizations(id)

Policies:

hr_proliant_cal_delete (DELETE)
hr_proliant_cal_insert (INSERT)
hr_proliant_cal_select (SELECT)
hr_proliant_cal_update (UPDATE)

`hr_proliant_payroll_details`

HR-44: Normalized payroll line items. RLS: ✅ enabled (2 policies)

Column	Type	Nullable	Default
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`payroll_run_id`	uuid	no
`detail_type`	text	no
`code`	text	no
`description`	text	yes
`amount`	numeric(12,2)	no
`hours`	numeric(8,2)	yes
`rate`	numeric(12,4)	yes
`created_at`	timestamp with time zone	no	now()

Foreign keys:

organization_id → pf_organizations(id)
payroll_run_id → hr_proliant_payroll_runs(id)

Policies:

hr_proliant_paydet_insert (INSERT)
hr_proliant_paydet_select (SELECT)

`hr_proliant_payroll_runs`

HR-44: Payroll run summaries from Proliant (one row per employee per run). RLS: ✅ enabled (3 policies)

Column	Type	Nullable	Default
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`employee_id`	uuid	no
`proliant_company_id`	text	no
`proliant_payroll_run_id`	text	no
`check_date`	date	no
`period_start`	date	no
`period_end`	date	no
`gross_pay`	numeric(12,2)	yes
`net_pay`	numeric(12,2)	yes
`total_taxes`	numeric(12,2)	yes
`total_deductions`	numeric(12,2)	yes
`synced_at`	timestamp with time zone	no	now()
`created_at`	timestamp with time zone	no	now()
`updated_at`	timestamp with time zone	no	now()

Foreign keys:

employee_id → hr_employees(id)
organization_id → pf_organizations(id)

Policies:

hr_proliant_payrun_insert (INSERT)
hr_proliant_payrun_select (SELECT)
hr_proliant_payrun_update (UPDATE)

`hr_proliant_push_log`

HR-44-EN-01: applied push idempotency keys; a re-run with an already-applied key is skipped so pushes are not double-applied to Proliant. RLS: ✅ enabled (3 policies)

Column	Type	Nullable	Default
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`integration_id`	uuid	no
`idempotency_key`	text	no
`resource`	text	no
`status`	text	no	’applied’::text
`vendor_ref`	text	yes
`custom_fields`	jsonb	no	’{}‘::jsonb
`created_at`	timestamp with time zone	no	now()
`updated_at`	timestamp with time zone	no	now()
`created_by`	uuid	yes
`updated_by`	uuid	yes

Foreign keys:

integration_id → hr_proliant_integrations(id)
organization_id → pf_organizations(id)

Policies:

hr_proliant_push_log_insert (INSERT)
hr_proliant_push_log_select (SELECT)
hr_proliant_push_log_update (UPDATE)

`hr_proliant_reconciliation`

HR-44 Stage 2: Pre-submit reconciliation of expected vs returned payroll totals, with variance override audit. RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`run_state_id`	uuid	no
`expected_gross`	numeric(14,2)	yes
`returned_gross`	numeric(14,2)	yes
`expected_headcount`	integer	yes
`returned_headcount`	integer	yes
`expected_deductions`	numeric(14,2)	yes
`returned_deductions`	numeric(14,2)	yes
`tolerance`	numeric(14,2)	no	0.00
`passed`	boolean	no	false
`overridden_by`	uuid	yes
`overridden_at`	timestamp with time zone	yes
`custom_fields`	jsonb	no	’{}‘::jsonb
`created_at`	timestamp with time zone	no	now()
`updated_at`	timestamp with time zone	no	now()
`created_by`	uuid	yes
`updated_by`	uuid	yes

Foreign keys:

organization_id → pf_organizations(id)
run_state_id → hr_proliant_run_state(id)

Policies:

hr_proliant_recon_delete (DELETE)
hr_proliant_recon_insert (INSERT)
hr_proliant_recon_select (SELECT)
hr_proliant_recon_update (UPDATE)

`hr_proliant_run_state`

HR-44 Stage 2: Embedded payroll run state machine (one row per org per calendar period). RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`integration_id`	uuid	no
`proliant_company_id`	text	no
`calendar_guid`	text	no
`phase`	text	no	’draft’::text
`verification_results`	jsonb	no	’[]‘::jsonb
`idempotency_key`	text	yes
`last_poll_at`	timestamp with time zone	yes
`error_message`	text	yes
`custom_fields`	jsonb	no	’{}‘::jsonb
`created_at`	timestamp with time zone	no	now()
`updated_at`	timestamp with time zone	no	now()
`created_by`	uuid	yes
`updated_by`	uuid	yes

Foreign keys:

integration_id → hr_proliant_integrations(id)
organization_id → pf_organizations(id)

Policies:

hr_proliant_runstate_delete (DELETE)
hr_proliant_runstate_insert (INSERT)
hr_proliant_runstate_select (SELECT)
hr_proliant_runstate_update (UPDATE)

`hr_proliant_sync_log`

HR-44: Immutable audit log (append-only; no UPDATE/DELETE policies). proliant_raw is PII-stripped. RLS: ✅ enabled (2 policies)

Column	Type	Nullable	Default
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`sync_run_id`	uuid	no
`employee_id`	uuid	yes
`entity_type`	text	no
`action`	text	no
`field_name`	text	yes
`old_value`	text	yes
`new_value`	text	yes
`conflict_status`	text	yes
`resolved_by`	uuid	yes
`resolved_at`	timestamp with time zone	yes
`proliant_raw`	jsonb	yes
`created_at`	timestamp with time zone	no	now()

Foreign keys:

employee_id → hr_employees(id)
organization_id → pf_organizations(id)
sync_run_id → hr_proliant_sync_runs(id)

Policies:

hr_proliant_log_insert (INSERT)
hr_proliant_log_select (SELECT)

`hr_proliant_sync_runs`

HR-44: Per-execution sync record. checkpoint JSONB enables resume after edge function timeout. RLS: ✅ enabled (3 policies)

Column	Type	Nullable	Default
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`integration_id`	uuid	no
`sync_type`	text	no
`direction`	text	no
`scope`	text	no
`trigger_type`	text	no
`triggered_by`	uuid	yes
`status`	text	no	’running’::text
`started_at`	timestamp with time zone	no	now()
`completed_at`	timestamp with time zone	yes
`records_processed`	integer	no	0
`records_created`	integer	no	0
`records_updated`	integer	no	0
`records_skipped`	integer	no	0
`records_failed`	integer	no	0
`records_conflicted`	integer	no	0
`error_message`	text	yes
`error_details`	jsonb	yes
`checkpoint`	jsonb	yes
`created_at`	timestamp with time zone	no	now()
`updated_at`	timestamp with time zone	no	now()

Foreign keys:

integration_id → hr_proliant_integrations(id)
organization_id → pf_organizations(id)

Policies:

hr_proliant_runs_insert (INSERT)
hr_proliant_runs_select (SELECT)
hr_proliant_runs_update (UPDATE)

`hr_reference_emails`

Audit log of emails sent for reference requests RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`reference_id`	uuid	no
`email_type`	text	no
`sent_to`	text	no
`sent_at`	timestamp with time zone	yes	now()
`message_id`	text	yes
`opened_at`	timestamp with time zone	yes
`clicked_at`	timestamp with time zone	yes
`bounced_at`	timestamp with time zone	yes
`bounce_reason`	text	yes
`created_at`	timestamp with time zone	no	now()
`created_by`	uuid	yes
`updated_at`	timestamp with time zone	no	now()

Foreign keys:

created_by → pf_profiles(id)
organization_id → pf_organizations(id)
reference_id → hr_references(id)

Policies:

hr_reference_emails_delete (DELETE)
hr_reference_emails_insert (INSERT)
hr_reference_emails_select (SELECT)
hr_reference_emails_update (UPDATE)

`hr_references`

Reference check requests and responses for candidates RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default	Comment
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`candidate_id`	uuid	no
`application_id`	uuid	yes
`referee_name`	text	no
`referee_email`	text	no
`referee_phone`	text	yes
`referee_title`	text	yes
`referee_company`	text	yes
`relationship_to_candidate`	text	no
`relationship_duration`	text	yes
`request_status`	text	no	’pending’::text	Workflow: pending → sent → viewed → in_progress → completed/expired/declined
`access_token`	text	no	encode(gen_random_bytes(32), ‘hex’::text)	DEPRECATED (v2026-02-08): Use access_token_hash for security. Plaintext tokens will be removed after migration period.
`requested_at`	timestamp with time zone	yes	now()
`sent_at`	timestamp with time zone	yes
`viewed_at`	timestamp with time zone	yes
`started_at`	timestamp with time zone	yes
`completed_at`	timestamp with time zone	yes
`expires_at`	timestamp with time zone	yes
`reminder_count`	integer	yes	0
`last_reminder_at`	timestamp with time zone	yes
`next_reminder_at`	timestamp with time zone	yes
`overall_performance_rating`	integer	yes
`reliability_rating`	integer	yes
`communication_rating`	integer	yes
`teamwork_rating`	integer	yes
`problem_solving_rating`	integer	yes
`strengths`	text	yes
`areas_for_improvement`	text	yes
`rehire_recommendation`	text	yes
`additional_comments`	text	yes
`reference_verified`	boolean	yes	false
`verified_at`	timestamp with time zone	yes
`verified_by`	uuid	yes
`verification_method`	text	yes
`verification_notes`	text	yes
`decline_reason`	text	yes
`ip_address`	text	yes
`user_agent`	text	yes
`custom_fields`	jsonb	no	’{}‘::jsonb
`created_at`	timestamp with time zone	no	now()
`updated_at`	timestamp with time zone	no	now()
`created_by`	uuid	yes
`updated_by`	uuid	yes
`deleted_at`	timestamp with time zone	yes
`access_token_hash`	text	yes

Foreign keys:

application_id → hr_applications(id)
candidate_id → hr_candidates(id)
created_by → pf_profiles(id)
organization_id → pf_organizations(id)
updated_by → pf_profiles(id)
verified_by → pf_profiles(id)

Policies:

hr_references_delete (DELETE)
hr_references_insert (INSERT)
hr_references_select (SELECT)
hr_references_update (UPDATE)

`hr_report_audit_events`

HR-42: append-only audit log of sensitive HR report exports (EEO-1, benefits census, etc.). Metadata only — never row-level PHI. INSERT only via hr_log_report_audit_event(); no UPDATE/DELETE. RLS: ✅ enabled (1 policies)

Column	Type	Nullable	Default
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`report_template_id`	text	no
`pf_report_run_id`	uuid	yes
`actor_user_id`	uuid	no
`report_type`	text	no
`parameters`	jsonb	no	’{}‘::jsonb
`row_count`	integer	yes
`completeness_percent`	numeric(5,2)	yes
`delivery_channel`	text	yes
`created_at`	timestamp with time zone	no	now()

Foreign keys:

actor_user_id → pf_profiles(id)
organization_id → pf_organizations(id)

Policies:

hr_report_audit_events_select (SELECT)

`hr_salary_bands`

Salary band definitions for positions RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`position_id`	uuid	no
`band_name`	text	no
`min_salary`	numeric	no
`mid_salary`	numeric	no
`max_salary`	numeric	no
`market_percentile`	integer	yes
`currency`	text	no	’USD’::text
`effective_date`	date	no
`end_date`	date	yes
`is_active`	boolean	no	true
`notes`	text	yes
`custom_fields`	jsonb	no	’{}‘::jsonb
`created_at`	timestamp with time zone	no	now()
`updated_at`	timestamp with time zone	no	now()
`created_by`	uuid	yes
`updated_by`	uuid	yes

Foreign keys:

created_by → pf_profiles(id)
organization_id → pf_organizations(id)
position_id → hr_positions(id)
updated_by → pf_profiles(id)

Policies:

hr_salary_bands_delete (DELETE)
hr_salary_bands_insert (INSERT)
hr_salary_bands_select (SELECT)
hr_salary_bands_update (UPDATE)

`hr_sharepoint_memberships`

Tracks SharePoint group memberships for employees (PF-63 Phase 3.2) RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`employee_id`	uuid	no
`department_id`	uuid	yes
`site_id`	text	no
`site_name`	text	no
`group_id`	text	no
`group_name`	text	no
`membership_status`	text	yes	’active’::text
`added_at`	timestamp with time zone	no	now()
`removed_at`	timestamp with time zone	yes
`error_message`	text	yes
`created_at`	timestamp with time zone	no	now()
`updated_at`	timestamp with time zone	no	now()
`created_by`	uuid	yes
`updated_by`	uuid	yes
`custom_fields`	jsonb	no	’{}‘::jsonb
`deleted_at`	timestamp with time zone	yes

Foreign keys:

created_by → pf_profiles(id)
department_id → pf_departments(id)
employee_id → hr_employees(id)
organization_id → pf_organizations(id)
updated_by → pf_profiles(id)

Policies:

hr_sharepoint_memberships_delete (DELETE)
hr_sharepoint_memberships_insert (INSERT)
hr_sharepoint_memberships_select (SELECT)
hr_sharepoint_memberships_update (UPDATE)

`hr_shift_assignments`

HR-04: Employee assignments to shifts RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`shift_id`	uuid	no
`employee_id`	uuid	no
`assignment_type`	assignment_type	no	’scheduled’::assignment_type
`status`	assignment_status	no	’assigned’::assignment_status
`confirmed_at`	timestamp with time zone	yes
`checked_in_at`	timestamp with time zone	yes
`checked_out_at`	timestamp with time zone	yes
`notes`	text	yes
`custom_fields`	jsonb	no	’{}‘::jsonb
`created_at`	timestamp with time zone	no	now()
`updated_at`	timestamp with time zone	no	now()
`created_by`	uuid	no
`updated_by`	uuid	yes

Foreign keys:

created_by → pf_profiles(id)
employee_id → hr_employees(id)
organization_id → pf_organizations(id)
shift_id → hr_shifts(id)
updated_by → pf_profiles(id)

Policies:

HR admins and assigned employees can update assignments (UPDATE)
HR admins and employees can insert assignments (INSERT)
HR admins can delete assignments (DELETE)
Users can view org shift assignments (SELECT)

`hr_shift_swap_requests`

HR-04: Shift swap workflow between employees RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`assignment_id`	uuid	no
`requesting_employee_id`	uuid	no
`target_employee_id`	uuid	yes
`status`	swap_status	no	’pending’::swap_status
`reason`	text	yes
`approved_by`	uuid	yes
`approved_at`	timestamp with time zone	yes
`custom_fields`	jsonb	no	’{}‘::jsonb
`created_at`	timestamp with time zone	no	now()
`updated_at`	timestamp with time zone	no	now()
`created_by`	uuid	no
`updated_by`	uuid	yes

Foreign keys:

approved_by → pf_profiles(id)
assignment_id → hr_shift_assignments(id)
created_by → pf_profiles(id)
organization_id → pf_organizations(id)
requesting_employee_id → hr_employees(id)
target_employee_id → hr_employees(id)
updated_by → pf_profiles(id)

Policies:

Employees can create swap requests (INSERT)
HR admins and managers can update swap requests (UPDATE)
HR admins can delete swap requests (DELETE)
Users can view related swap requests (SELECT)

`hr_shift_templates`

HR-04: Reusable shift patterns for scheduling RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`site_id`	uuid	yes
`name`	text	no
`shift_type`	shift_type	no	’regular’::shift_type
`start_time`	time without time zone	no
`end_time`	time without time zone	no
`duration_hours`	numeric(4,2)	no
`required_position_id`	uuid	yes
`required_credential_types`	uuid[]	yes	’{}‘::uuid[]
`min_staff_count`	integer	yes	1
`max_staff_count`	integer	yes
`is_active`	boolean	no	true
`notes`	text	yes
`custom_fields`	jsonb	no	’{}‘::jsonb
`created_at`	timestamp with time zone	no	now()
`updated_at`	timestamp with time zone	no	now()
`created_by`	uuid	no
`updated_by`	uuid	yes

Foreign keys:

created_by → pf_profiles(id)
organization_id → pf_organizations(id)
required_position_id → hr_positions(id)
site_id → pf_sites(id)
updated_by → pf_profiles(id)

Policies:

HR admins can delete shift templates (DELETE)
HR admins can insert shift templates (INSERT)
HR admins can update shift templates (UPDATE)
Users can view org shift templates (SELECT)

`hr_shifts`

HR-04: Individual shift instances on specific dates RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default	Comment
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`site_id`	uuid	yes
`template_id`	uuid	yes
`shift_date`	date	no
`start_time`	time without time zone	no
`end_time`	time without time zone	no
`required_position_id`	uuid	yes
`status`	shift_status	no	’open’::shift_status
`notes`	text	yes
`custom_fields`	jsonb	no	’{}‘::jsonb
`created_at`	timestamp with time zone	no	now()
`updated_at`	timestamp with time zone	no	now()
`created_by`	uuid	no
`updated_by`	uuid	yes
`required_staff`	integer	yes		Calculated staff requirement from workload driver
`calculated_by_driver_id`	uuid	yes		Reference to the workload driver used for calculation
`staff_calculation_notes`	text	yes		Notes about the staffing calculation

Foreign keys:

calculated_by_driver_id → hr_workload_drivers(id)
created_by → pf_profiles(id)
organization_id → pf_organizations(id)
required_position_id → hr_positions(id)
site_id → pf_sites(id)
template_id → hr_shift_templates(id)
updated_by → pf_profiles(id)

Policies:

HR admins can delete shifts (DELETE)
HR admins can insert shifts (INSERT)
HR admins can update shifts (UPDATE)
Users can view org shifts (SELECT)

`hr_site_geofence`

HR-05-EN03: Geofence configuration per site for time punch location validation RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default	Comment
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`site_id`	uuid	no
`center_lat`	numeric(10,7)	no
`center_lng`	numeric(10,7)	no
`radius_meters`	numeric(10,2)	no	100.00
`polygon_geojson`	jsonb	yes
`geofence_behavior`	text	no	’warn’::text	warn = show warning but allow punch; block = prevent punch outside geofence
`geofence_auto_clock_out_enabled`	boolean	no	false
`is_active`	boolean	no	true
`custom_fields`	jsonb	no	’{}‘::jsonb	Organization-specific metadata
`created_at`	timestamp with time zone	no	now()
`updated_at`	timestamp with time zone	no	now()
`created_by`	uuid	yes
`updated_by`	uuid	yes

Foreign keys:

created_by → users(id)
organization_id → pf_organizations(id)
site_id → pf_sites(id)
updated_by → users(id)

Policies:

hr_site_geofence_delete (DELETE)
hr_site_geofence_insert (INSERT)
hr_site_geofence_select (SELECT)
hr_site_geofence_update (UPDATE)

`hr_skills_assessments`

Skills validation and assessment records RLS: ✅ enabled (3 policies)

Column	Type	Nullable	Default
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`employee_id`	uuid	no
`skill_id`	uuid	no
`assessment_type`	text	no
`assessment_date`	date	no
`assessed_by`	uuid	yes
`proficiency_level`	text	no
`score`	numeric	yes
`passed`	boolean	no
`notes`	text	yes
`custom_fields`	jsonb	no	’{}‘::jsonb
`created_at`	timestamp with time zone	no	now()
`updated_at`	timestamp with time zone	no	now()
`created_by`	uuid	yes
`updated_by`	uuid	yes
`deleted_at`	timestamp with time zone	yes

Foreign keys:

assessed_by → pf_profiles(id)
created_by → pf_profiles(id)
employee_id → hr_employees(id)
organization_id → pf_organizations(id)
skill_id → hr_skills_library(id)
updated_by → pf_profiles(id)

Policies:

hr_skills_assessments_insert (INSERT)
hr_skills_assessments_select (SELECT)
hr_skills_assessments_update (UPDATE)

`hr_skills_library`

Centralized skills catalog for the organization RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default	Comment
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`name`	text	no
`code`	text	no
`description`	text	yes
`category`	text	no
`parent_skill_id`	uuid	yes
`is_certification`	boolean	no	false
`requires_evidence`	boolean	no	false
`proficiency_levels`	text[]	yes	ARRAY[‘beginner’::text, ‘intermediate’::text, ‘advanced’::text, ‘expert’::text]	Custom proficiency levels for this skill
`is_active`	boolean	no	true
`custom_fields`	jsonb	no	’{}‘::jsonb
`created_at`	timestamp with time zone	no	now()
`updated_at`	timestamp with time zone	no	now()
`created_by`	uuid	yes
`updated_by`	uuid	yes
`deleted_at`	timestamp with time zone	yes

Foreign keys:

created_by → pf_profiles(id)
organization_id → pf_organizations(id)
parent_skill_id → hr_skills_library(id)
updated_by → pf_profiles(id)

Policies:

hr_skills_library_delete (DELETE)
hr_skills_library_insert (INSERT)
hr_skills_library_select (SELECT)
hr_skills_library_update (UPDATE)

`hr_sms_consent_logs`

TCPA compliance: Tracks explicit SMS consent for background check notifications (HR-09-P5 Phase 5.2c) RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default	Comment
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`candidate_id`	uuid	yes
`application_id`	uuid	yes
`background_check_id`	uuid	yes
`phone_number`	text	no		PII: Phone number for SMS consent. Subject to TCPA compliance requirements. Consider encryption via Vault for enhanced protection.
`consent_type`	text	no	’background_check_notifications’::text
`consent_given`	boolean	no
`consent_text`	text	no
`captured_at`	timestamp with time zone	no	now()
`captured_by`	uuid	yes
`ip_address`	text	yes
`user_agent`	text	yes
`opted_out_at`	timestamp with time zone	yes
`opt_out_method`	text	yes
`created_at`	timestamp with time zone	no	now()
`updated_at`	timestamp with time zone	no	now()
`created_by`	uuid	yes
`updated_by`	uuid	yes
`deleted_at`	timestamp with time zone	yes
`custom_fields`	jsonb	no	’{}‘::jsonb
`site_id`	uuid	yes

Foreign keys:

application_id → hr_applications(id)
background_check_id → hr_background_checks(id)
candidate_id → hr_candidates(id)
captured_by → users(id)
created_by → users(id)
organization_id → pf_organizations(id)
site_id → pf_sites(id)
updated_by → users(id)

Policies:

hr_sms_consent_logs_delete (DELETE)
hr_sms_consent_logs_insert (INSERT)
hr_sms_consent_logs_select (SELECT)
hr_sms_consent_logs_update (UPDATE)

`hr_ssn_access_log`

RLS: ✅ enabled (2 policies)

Column	Type	Nullable	Default
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`dependent_id`	uuid	no
`accessed_by`	uuid	no
`access_type`	text	no
`access_reason`	text	yes
`ip_address`	inet	yes
`user_agent`	text	yes
`accessed_at`	timestamp with time zone	no	now()
`created_at`	timestamp with time zone	yes	now()
`updated_at`	timestamp with time zone	yes	now()

Foreign keys:

accessed_by → pf_profiles(id)
dependent_id → hr_benefits_dependents(id)
organization_id → pf_organizations(id)

Policies:

hr_ssn_access_log_insert (INSERT)
hr_ssn_access_log_select (SELECT)

`hr_staffing_agencies`

HR-34: Staffing agencies providing contract/temp workforce RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default	Comment
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`name`	text	no
`contact_name`	text	yes
`contact_email`	text	yes
`contact_phone`	text	yes
`contract_storage_path`	text	yes
`standard_markup_pct`	numeric(5,2)	yes
`specialties`	text[]	yes
`is_active`	boolean	no	true
`notes`	text	yes
`custom_fields`	jsonb	no	’{}‘::jsonb	Organization-specific metadata
`created_at`	timestamp with time zone	no	now()
`updated_at`	timestamp with time zone	no	now()
`created_by`	uuid	yes
`updated_by`	uuid	yes

Foreign keys:

created_by → users(id)
organization_id → pf_organizations(id)
updated_by → users(id)

Policies:

hr_staffing_agencies_delete (DELETE)
hr_staffing_agencies_insert (INSERT)
hr_staffing_agencies_select (SELECT)
hr_staffing_agencies_update (UPDATE)

`hr_succession_plans`

HR-16: Tracks succession planning for key positions with readiness scores RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default	Comment
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`position_id`	uuid	no
`incumbent_employee_id`	uuid	yes
`successor_employee_id`	uuid	yes
`readiness_level`	text	no	’ready_3_plus_years’::text
`readiness_score`	numeric(5,2)	yes	0
`priority`	text	no	’medium’::text
`status`	text	no	’active’::text
`risk_of_loss`	text	yes	’medium’::text
`impact_of_loss`	text	yes	’medium’::text
`development_goals`	text	yes
`notes`	text	yes
`target_ready_date`	date	yes
`last_reviewed_at`	timestamp with time zone	yes
`reviewed_by`	uuid	yes
`custom_fields`	jsonb	no	’{}‘::jsonb	Organization-specific metadata
`created_at`	timestamp with time zone	no	now()
`updated_at`	timestamp with time zone	no	now()
`created_by`	uuid	yes
`updated_by`	uuid	yes
`deleted_at`	timestamp with time zone	yes

Foreign keys:

created_by → pf_profiles(id)
incumbent_employee_id → hr_employees(id)
organization_id → pf_organizations(id)
position_id → hr_positions(id)
reviewed_by → pf_profiles(id)
successor_employee_id → hr_employees(id)
updated_by → pf_profiles(id)

Policies:

hr_succession_plans_delete (DELETE)
hr_succession_plans_insert (INSERT)
hr_succession_plans_select (SELECT)
hr_succession_plans_update (UPDATE)

`hr_survey_responses`

HR-17: Employee survey responses RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default	Comment
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`survey_id`	uuid	no
`employee_id`	uuid	yes		NULL for anonymous surveys (set by anonymization trigger)
`form_submission_id`	uuid	yes
`response_data`	jsonb	no
`completion_percentage`	integer	yes	0
`status`	text	yes	’in_progress’::text
`completed_at`	timestamp with time zone	yes
`ip_address`	text	yes		NOT populated for anonymous surveys (privacy protection)
`user_agent`	text	yes
`custom_fields`	jsonb	no	’{}‘::jsonb
`created_at`	timestamp with time zone	no	now()
`updated_at`	timestamp with time zone	no	now()

Foreign keys:

employee_id → hr_employees(id)
form_submission_id → fw_form_submissions(id)
organization_id → pf_organizations(id)
survey_id → hr_engagement_surveys(id)

Policies:

hr_survey_responses_delete (DELETE)
hr_survey_responses_insert (INSERT)
hr_survey_responses_select (SELECT)
hr_survey_responses_update (UPDATE)

`hr_talent_pipelines`

HR-16: High-potential employee talent pipeline entries RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default	Comment
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`employee_id`	uuid	no
`target_position_id`	uuid	yes
`category`	text	no	’high_potential’::text
`readiness_level`	text	no	’ready_3_plus_years’::text
`readiness_score`	numeric(5,2)	yes	0
`status`	text	no	’active’::text
`strengths`	text	yes
`development_areas`	text	yes
`mentor_employee_id`	uuid	yes
`next_review_date`	date	yes
`last_reviewed_at`	timestamp with time zone	yes
`reviewed_by`	uuid	yes
`notes`	text	yes
`custom_fields`	jsonb	no	’{}‘::jsonb	Organization-specific metadata
`created_at`	timestamp with time zone	no	now()
`updated_at`	timestamp with time zone	no	now()
`created_by`	uuid	yes
`updated_by`	uuid	yes
`deleted_at`	timestamp with time zone	yes

Foreign keys:

created_by → pf_profiles(id)
employee_id → hr_employees(id)
mentor_employee_id → hr_employees(id)
organization_id → pf_organizations(id)
reviewed_by → pf_profiles(id)
target_position_id → hr_positions(id)
updated_by → pf_profiles(id)

Policies:

hr_talent_pipelines_delete (DELETE)
hr_talent_pipelines_insert (INSERT)
hr_talent_pipelines_select (SELECT)
hr_talent_pipelines_update (UPDATE)

`hr_tax_documents`

Tax documents (W-2, 1099, 1095-C) for employees RLS: ✅ enabled (6 policies)

Column	Type	Nullable	Default	Comment
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`employee_id`	uuid	no
`document_type`	text	no		Type of tax document: w2, 1099, 1095-c, other
`tax_year`	integer	no		Tax year the document is for (e.g., 2025)
`document_id`	uuid	yes		Reference to pf_documents for file storage
`status`	text	no	’pending’::text	Document status: pending, available, viewed, downloaded
`available_at`	timestamp with time zone	yes
`first_viewed_at`	timestamp with time zone	yes
`first_downloaded_at`	timestamp with time zone	yes
`form_data`	jsonb	yes	’{}‘::jsonb	JSONB containing form field values (e.g., W-2 box amounts)
`correction_of`	uuid	yes		References original document if this is a correction (W-2C)
`is_corrected`	boolean	yes	false
`custom_fields`	jsonb	no	’{}‘::jsonb
`created_at`	timestamp with time zone	no	now()
`updated_at`	timestamp with time zone	no	now()
`created_by`	uuid	yes
`updated_by`	uuid	yes

Foreign keys:

correction_of → hr_tax_documents(id)
created_by → pf_profiles(id)
document_id → pf_documents(id)
employee_id → hr_employees(id)
organization_id → pf_organizations(id)
updated_by → pf_profiles(id)

Policies:

hr_tax_documents_admin_delete (DELETE)
hr_tax_documents_admin_insert (INSERT)
hr_tax_documents_admin_select (SELECT)
hr_tax_documents_admin_update (UPDATE)
hr_tax_documents_self_select (SELECT)
hr_tax_documents_self_update_status (UPDATE)

`hr_tax_form_items`

RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`tax_form_run_id`	uuid	no
`employee_id`	uuid	yes
`form_data`	jsonb	no	’{}‘::jsonb
`pdf_file_path`	text	yes
`pdf_generated_at`	timestamp with time zone	yes
`distributed_to_employee`	boolean	yes	false
`distributed_at`	timestamp with time zone	yes
`tax_document_id`	uuid	yes
`is_correction`	boolean	yes	false
`original_form_item_id`	uuid	yes
`custom_fields`	jsonb	no	’{}‘::jsonb
`created_at`	timestamp with time zone	no	now()
`updated_at`	timestamp with time zone	no	now()
`created_by`	uuid	yes
`updated_by`	uuid	yes

Foreign keys:

created_by → pf_profiles(id)
employee_id → hr_employees(id)
organization_id → pf_organizations(id)
original_form_item_id → hr_tax_form_items(id)
tax_document_id → hr_tax_documents(id)
tax_form_run_id → hr_tax_form_runs(id)
updated_by → pf_profiles(id)

Policies:

hr_tax_form_items_delete (DELETE)
hr_tax_form_items_insert (INSERT)
hr_tax_form_items_select (SELECT)
hr_tax_form_items_update (UPDATE)

`hr_tax_form_runs`

RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default	Comment
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`form_type`	text	no
`tax_year`	integer	no
`quarter`	integer	yes
`status`	text	no	’draft’::text
`form_count`	integer	yes	0
`total_wages`	numeric(14,2)	yes
`total_tax`	numeric(14,2)	yes
`finalized_at`	timestamp with time zone	yes
`finalized_by`	uuid	yes
`filed_at`	timestamp with time zone	yes
`filed_by`	uuid	yes
`notes`	text	yes
`custom_fields`	jsonb	no	’{}‘::jsonb
`created_at`	timestamp with time zone	no	now()
`updated_at`	timestamp with time zone	no	now()
`created_by`	uuid	yes
`updated_by`	uuid	yes
`confirmation_number`	text	yes		IRS/state filing confirmation number recorded when marking as filed
`filing_method`	text	yes	’manual’::text	Filing method: manual (print/mail) or efile (electronic). Default manual.
`efile_submitted_at`	timestamp with time zone	yes		Timestamp when e-file was submitted to provider.
`efile_acknowledgment_ref`	text	yes		Provider acknowledgment/receipt reference number.
`efile_status`	text	yes		E-file status: pending, accepted, or rejected. NULL if manual filing.
`efile_error_message`	character varying(500)	yes		Sanitized error message from e-file provider (max 500 chars). NULL if accepted.

Foreign keys:

created_by → pf_profiles(id)
filed_by → pf_profiles(id)
finalized_by → pf_profiles(id)
organization_id → pf_organizations(id)
updated_by → pf_profiles(id)

Policies:

hr_tax_form_runs_delete (DELETE)
hr_tax_form_runs_insert (INSERT)
hr_tax_form_runs_select (SELECT)
hr_tax_form_runs_update (UPDATE)

`hr_tax_form_state_items`

HR-PAY-04: Per-state W-2 wage/tax breakdown for multi-state employees. RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default	Comment
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`tax_form_item_id`	uuid	no
`state_code`	text	no		Two-letter US state code (e.g. AZ, CA).
`state_wages`	numeric(12,2)	no	0
`state_tax_withheld`	numeric(12,2)	no	0
`state_employer_id`	text	yes		Employer state tax ID number for this state.
`custom_fields`	jsonb	no	’{}‘::jsonb	Organization-specific metadata for state tax items.
`created_at`	timestamp with time zone	no	now()
`updated_at`	timestamp with time zone	no	now()
`created_by`	uuid	yes
`updated_by`	uuid	yes

Foreign keys:

created_by → users(id)
organization_id → pf_organizations(id)
tax_form_item_id → hr_tax_form_items(id)
updated_by → users(id)

Policies:

hr_tax_form_state_items_delete (DELETE)
hr_tax_form_state_items_insert (INSERT)
hr_tax_form_state_items_select (SELECT)
hr_tax_form_state_items_update (UPDATE)

`hr_team_members`

RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default
`id`	uuid	no	gen_random_uuid()
`team_id`	uuid	no
`employee_id`	uuid	no
`role`	text	yes
`joined_at`	timestamp with time zone	no	now()
`left_at`	timestamp with time zone	yes
`created_at`	timestamp with time zone	no	now()
`created_by`	uuid	no
`organization_id`	uuid	no
`updated_at`	timestamp with time zone	yes	now()
`updated_by`	uuid	yes

Foreign keys:

created_by → pf_profiles(id)
employee_id → hr_employees(id)
organization_id → pf_organizations(id)
team_id → hr_employee_teams(id)

Policies:

Users can view team members (SELECT)
hr_team_members_delete (DELETE)
hr_team_members_insert (INSERT)
hr_team_members_update (UPDATE)

`hr_teams_memberships`

Tracks Microsoft Teams memberships for employees (PF-63 Phase 3.1) RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`employee_id`	uuid	no
`department_id`	uuid	yes
`team_id`	text	no
`team_name`	text	no
`channel_id`	text	yes
`channel_name`	text	yes
`membership_id`	text	yes
`membership_status`	text	yes	’active’::text
`added_at`	timestamp with time zone	no	now()
`removed_at`	timestamp with time zone	yes
`error_message`	text	yes
`created_at`	timestamp with time zone	no	now()
`updated_at`	timestamp with time zone	no	now()
`created_by`	uuid	yes
`updated_by`	uuid	yes
`custom_fields`	jsonb	no	’{}‘::jsonb
`deleted_at`	timestamp with time zone	yes

Foreign keys:

created_by → pf_profiles(id)
department_id → pf_departments(id)
employee_id → hr_employees(id)
organization_id → pf_organizations(id)
updated_by → pf_profiles(id)

Policies:

hr_teams_memberships_delete (DELETE)
hr_teams_memberships_insert (INSERT)
hr_teams_memberships_select (SELECT)
hr_teams_memberships_update (UPDATE)

`hr_time_exceptions`

RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default	Comment
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`employee_id`	uuid	no
`timesheet_entry_id`	uuid	yes
`exception_type`	text	no
`exception_date`	date	no
`details`	jsonb	no	’{}‘::jsonb
`severity`	text	no	’warning’::text
`status`	text	no	’open’::text
`resolved_by`	uuid	yes
`resolved_at`	timestamp with time zone	yes
`resolution_notes`	text	yes
`custom_fields`	jsonb	no	’{}‘::jsonb	Organization-specific metadata for time exceptions. Example use cases: - {“supervisor_notified”: true, “notification_sent_at”: “2025-01-15T09:00:00Z”} - {“union_representative_involved”: true, “grievance_filed”: false} - {“corrective_action_required”: true, “action_type”: “verbal_warning”} - {“overtime_pre_approved”: true, “approval_reference”: “OT-2025-001”} - {“external_system_id”: “HRIS-12345”, “imported_at”: “2025-01-01”}
`created_at`	timestamp with time zone	no	now()
`updated_at`	timestamp with time zone	no	now()
`created_by`	uuid	yes		User who created this exception record
`updated_by`	uuid	yes		User who last updated this exception record

Foreign keys:

created_by → pf_profiles(id)
employee_id → hr_employees(id)
organization_id → pf_organizations(id)
resolved_by → pf_profiles(id)
timesheet_entry_id → hr_timesheet_entries(id)
updated_by → pf_profiles(id)

Policies:

hr_time_exceptions_delete (DELETE)
hr_time_exceptions_insert (INSERT)
hr_time_exceptions_select (SELECT)
hr_time_exceptions_update (UPDATE)

`hr_time_punches`

RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default	Comment
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`employee_id`	uuid	no
`shift_assignment_id`	uuid	yes
`punch_type`	text	no
`punch_time`	timestamp with time zone	no
`location_lat`	numeric(10,7)	yes
`location_lng`	numeric(10,7)	yes
`device_info`	jsonb	yes	’{}‘::jsonb
`photo_url`	text	yes
`edited_by`	uuid	yes
`edited_at`	timestamp with time zone	yes
`edit_reason`	text	yes
`created_at`	timestamp with time zone	no	now()
`created_by`	uuid	yes
`custom_fields`	jsonb	no	’{}‘::jsonb	Organization-specific metadata (e.g., {“device_id”: “KIOSK-01”, “supervisor_override”: true, “job_site”: “Site A”})
`updated_at`	timestamp with time zone	yes	now()
`updated_by`	uuid	yes

Foreign keys:

created_by → pf_profiles(id)
edited_by → pf_profiles(id)
employee_id → hr_employees(id)
organization_id → pf_organizations(id)
shift_assignment_id → hr_shift_assignments(id)

Policies:

Employees can create own punches (INSERT)
Employees can view own punches (SELECT)
HR admins can delete punches (DELETE)
Managers and HR can edit punches (UPDATE)

`hr_timesheet_entries`

RLS: ✅ enabled (5 policies)

Column	Type	Nullable	Default	Comment
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`timesheet_id`	uuid	no
`work_date`	date	no
`clock_in_time`	timestamp with time zone	yes
`clock_out_time`	timestamp with time zone	yes
`break_duration_minutes`	integer	yes	0
`total_hours`	numeric(6,2)	yes	0
`punch_in_id`	uuid	yes
`punch_out_id`	uuid	yes
`notes`	text	yes
`exception_type`	text	yes
`created_at`	timestamp with time zone	no	now()
`updated_at`	timestamp with time zone	no	now()
`custom_fields`	jsonb	no	’{}‘::jsonb	Organization-specific metadata (e.g., {“project_code”: “PRJ-001”, “task_code”: “DEV”, “billable”: true, “client_id”: “CLIENT-123”})

Foreign keys:

organization_id → pf_organizations(id)
punch_in_id → hr_time_punches(id)
punch_out_id → hr_time_punches(id)
timesheet_id → hr_timesheets(id)

Policies:

Users can view accessible entries (SELECT)
hr_timesheet_entries_delete (DELETE)
hr_timesheet_entries_insert (INSERT)
hr_timesheet_entries_select (SELECT)
hr_timesheet_entries_update (UPDATE)

`hr_timesheets`

RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default	Comment
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`employee_id`	uuid	no
`period_start`	date	no
`period_end`	date	no
`total_hours_worked`	numeric(6,2)	yes	0
`regular_hours`	numeric(6,2)	yes	0
`overtime_hours`	numeric(6,2)	yes	0
`pto_hours`	numeric(6,2)	yes	0
`status`	text	no	’draft’::text
`submitted_at`	timestamp with time zone	yes
`approved_by`	uuid	yes
`approved_at`	timestamp with time zone	yes
`rejection_reason`	text	yes
`created_at`	timestamp with time zone	no	now()
`updated_at`	timestamp with time zone	no	now()
`created_by`	uuid	yes
`updated_by`	uuid	yes
`custom_fields`	jsonb	no	’{}‘::jsonb	Organization-specific metadata (e.g., {“payroll_batch_id”: “PAY-2025-01”, “cost_center”: “IT-001”, “export_status”: “pending”})

Foreign keys:

approved_by → pf_profiles(id)
created_by → pf_profiles(id)
employee_id → hr_employees(id)
organization_id → pf_organizations(id)
updated_by → pf_profiles(id)

Policies:

Employees can view own timesheets (SELECT)
HR admins can delete timesheets (DELETE)
Managers and HR can update timesheets (UPDATE)
hr_timesheets_org_insert (INSERT)

`hr_total_compensation_statements`

Total compensation statements for employees RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`employee_id`	uuid	no
`statement_year`	integer	no
`statement_period`	text	yes
`base_salary`	numeric	no	0
`bonuses`	numeric	no	0
`benefits_value`	numeric	no	0
`retirement_contributions`	numeric	no	0
`other_compensation`	numeric	no	0
`total_compensation`	numeric	no	0
`currency`	text	no	’USD’::text
`status`	text	no	’draft’::text
`generated_at`	timestamp with time zone	yes
`sent_at`	timestamp with time zone	yes
`acknowledged_at`	timestamp with time zone	yes
`notes`	text	yes
`custom_fields`	jsonb	no	’{}‘::jsonb
`created_at`	timestamp with time zone	no	now()
`updated_at`	timestamp with time zone	no	now()
`created_by`	uuid	yes
`updated_by`	uuid	yes

Foreign keys:

created_by → pf_profiles(id)
employee_id → hr_employees(id)
organization_id → pf_organizations(id)
updated_by → pf_profiles(id)

Policies:

hr_tcs_delete (DELETE)
hr_tcs_insert (INSERT)
hr_tcs_select (SELECT)
hr_tcs_update (UPDATE)

`hr_workload_accuracy_tracking`

RLS: ✅ enabled (2 policies)

Column	Type	Nullable	Default
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`driver_id`	uuid	no
`tracking_period_start`	date	no
`tracking_period_end`	date	no
`predicted_total`	numeric(10,2)	no
`actual_total`	numeric(10,2)	yes
`accuracy_percentage`	numeric(5,2)	yes
`sample_count`	integer	no	0
`notes`	text	yes
`custom_fields`	jsonb	no	’{}‘::jsonb
`created_at`	timestamp with time zone	no	now()
`updated_at`	timestamp with time zone	yes	now()

Foreign keys:

driver_id → hr_workload_drivers(id)
organization_id → pf_organizations(id)

Policies:

hr_workload_accuracy_tracking_insert (INSERT)
hr_workload_accuracy_tracking_select (SELECT)

`hr_workload_approvals`

RLS: ✅ enabled (3 policies)

Column	Type	Nullable	Default
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`driver_id`	uuid	yes
`scenario_id`	uuid	yes
`approval_type`	text	no
`status`	hr_workload_approval_status	no	’pending’::hr_workload_approval_status
`requested_by`	uuid	no
`requested_at`	timestamp with time zone	no	now()
`approved_by`	uuid	yes
`approved_at`	timestamp with time zone	yes
`rejected_by`	uuid	yes
`rejected_at`	timestamp with time zone	yes
`rejection_reason`	text	yes
`expires_at`	timestamp with time zone	yes
`custom_fields`	jsonb	no	’{}‘::jsonb
`created_at`	timestamp with time zone	no	now()
`updated_at`	timestamp with time zone	no	now()

Foreign keys:

approved_by → pf_profiles(id)
driver_id → hr_workload_drivers(id)
organization_id → pf_organizations(id)
rejected_by → pf_profiles(id)
requested_by → pf_profiles(id)
scenario_id → hr_workload_scenarios(id)

Policies:

hr_workload_approvals_insert (INSERT)
hr_workload_approvals_select (SELECT)
hr_workload_approvals_update (UPDATE)

`hr_workload_calculations`

RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`site_id`	uuid	yes
`driver_id`	uuid	no
`shift_id`	uuid	yes
`shift_template_id`	uuid	yes
`calculation_date`	date	no
`input_metrics`	jsonb	no	’{}‘::jsonb
`calculated_staff_need`	numeric(10,2)	no
`actual_staff_count`	integer	yes
`variance`	numeric(10,2)	yes
`variance_percent`	numeric(5,2)	yes
`notes`	text	yes
`custom_fields`	jsonb	no	’{}‘::jsonb
`created_at`	timestamp with time zone	no	now()
`updated_at`	timestamp with time zone	no	now()

Foreign keys:

driver_id → hr_workload_drivers(id)
organization_id → pf_organizations(id)
shift_id → hr_shifts(id)
shift_template_id → hr_shift_templates(id)
site_id → pf_sites(id)

Policies:

hr_workload_calculations_delete (DELETE)
hr_workload_calculations_insert (INSERT)
hr_workload_calculations_select (SELECT)
hr_workload_calculations_update (UPDATE)

`hr_workload_driver_versions`

RLS: ✅ enabled (2 policies)

Column	Type	Nullable	Default
`id`	uuid	no	gen_random_uuid()
`driver_id`	uuid	no
`version_number`	integer	no
`config_snapshot`	jsonb	no
`change_reason`	text	yes
`changed_at`	timestamp with time zone	no	now()
`changed_by`	uuid	no
`custom_fields`	jsonb	no	’{}‘::jsonb
`created_at`	timestamp with time zone	yes	now()
`updated_at`	timestamp with time zone	yes	now()

Foreign keys:

changed_by → pf_profiles(id)
driver_id → hr_workload_drivers(id)

Policies:

hr_workload_driver_versions_insert (INSERT)
hr_workload_driver_versions_select (SELECT)

`hr_workload_drivers`

RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`site_id`	uuid	yes
`name`	text	no
`driver_type`	hr_driver_type	no	’fixed’::hr_driver_type
`description`	text	yes
`config`	jsonb	no	’{}‘::jsonb
`applies_to`	hr_driver_scope	no	’organization’::hr_driver_scope
`shift_template_id`	uuid	yes
`position_id`	uuid	yes
`aggregation_method`	hr_aggregation_method	no	’priority’::hr_aggregation_method
`priority`	integer	no	100
`parent_driver_id`	uuid	yes
`version`	integer	no	1
`is_template`	boolean	no	false
`template_category`	text	yes
`accuracy_score`	numeric(5,2)	yes
`effective_date`	date	no	CURRENT_DATE
`end_date`	date	yes
`is_active`	boolean	no	true
`custom_fields`	jsonb	no	’{}‘::jsonb
`created_at`	timestamp with time zone	no	now()
`updated_at`	timestamp with time zone	no	now()
`created_by`	uuid	no
`updated_by`	uuid	yes

Foreign keys:

created_by → pf_profiles(id)
organization_id → pf_organizations(id)
parent_driver_id → hr_workload_drivers(id)
position_id → hr_positions(id)
shift_template_id → hr_shift_templates(id)
site_id → pf_sites(id)
updated_by → pf_profiles(id)

Policies:

hr_workload_drivers_delete (DELETE)
hr_workload_drivers_insert (INSERT)
hr_workload_drivers_select (SELECT)
hr_workload_drivers_update (UPDATE)

`hr_workload_forecasts`

RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default	Comment
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`site_id`	uuid	yes
`forecast_date`	date	no
`forecast_type`	text	no
`projected_census`	integer	yes
`projected_acuity`	jsonb	yes	’{}‘::jsonb
`projected_pto_rate`	numeric(5,4)	yes
`other_metrics`	jsonb	yes	’{}‘::jsonb
`projected_staff_count`	integer	no
`driver_id`	uuid	yes
`confidence_level`	numeric(4,3)	yes	0.750
`historical_accuracy`	numeric(4,3)	yes
`actual_staff_count`	integer	yes
`notes`	text	yes
`custom_fields`	jsonb	yes	’{}‘::jsonb
`created_at`	timestamp with time zone	no	now()
`updated_at`	timestamp with time zone	no	now()
`created_by`	uuid	yes
`updated_by`	uuid	yes		User who last updated this record

Foreign keys:

created_by → pf_profiles(id)
driver_id → hr_workload_drivers(id)
organization_id → pf_organizations(id)
site_id → pf_sites(id)
updated_by → users(id)

Policies:

hr_workload_forecasts_delete (DELETE)
hr_workload_forecasts_insert (INSERT)
hr_workload_forecasts_select (SELECT)
hr_workload_forecasts_update (UPDATE)

`hr_workload_pto_patterns`

RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`site_id`	uuid	yes
`position_id`	uuid	yes
`day_of_week`	integer	yes
`month`	integer	yes
`holiday_proximity`	integer	yes
`average_pto_rate`	numeric(5,4)	no	0
`sample_size`	integer	no	0
`confidence_score`	numeric(5,2)	yes
`year`	integer	yes
`custom_fields`	jsonb	no	’{}‘::jsonb
`created_at`	timestamp with time zone	no	now()
`updated_at`	timestamp with time zone	no	now()

Foreign keys:

organization_id → pf_organizations(id)
position_id → hr_positions(id)
site_id → pf_sites(id)

Policies:

hr_workload_pto_patterns_delete (DELETE)
hr_workload_pto_patterns_insert (INSERT)
hr_workload_pto_patterns_select (SELECT)
hr_workload_pto_patterns_update (UPDATE)

`hr_workload_scenarios`

RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`site_id`	uuid	yes
`name`	text	no
`description`	text	yes
`base_driver_id`	uuid	yes
`scenario_config`	jsonb	no	’{}‘::jsonb
`projected_results`	jsonb	yes
`is_published`	boolean	no	false
`custom_fields`	jsonb	no	’{}‘::jsonb
`created_at`	timestamp with time zone	no	now()
`updated_at`	timestamp with time zone	no	now()
`created_by`	uuid	no
`updated_by`	uuid	yes

Foreign keys:

base_driver_id → hr_workload_drivers(id)
created_by → pf_profiles(id)
organization_id → pf_organizations(id)
site_id → pf_sites(id)
updated_by → pf_profiles(id)

Policies:

hr_workload_scenarios_delete (DELETE)
hr_workload_scenarios_insert (INSERT)
hr_workload_scenarios_select (SELECT)
hr_workload_scenarios_update (UPDATE)

Governance & Risk Schema Reference IT Service Management Schema Reference

​Entity relationships

​Tables

​hr_aca_compliance

​hr_aca_employee_coverage

​hr_analytics_cache

​hr_applications

​hr_availability_preferences

​hr_background_check_providers

​hr_background_check_webhook_dlq

​hr_background_check_webhooks

​hr_background_checks

​hr_benefits_dependents

​hr_benefits_eligibility_rules

​hr_benefits_enrollment_dependents

​hr_benefits_enrollments

​hr_benefits_guides

​hr_benefits_life_events

​hr_benefits_open_enrollment_periods

​hr_benefits_plans

​hr_candidate_communications

​hr_candidate_eeo_data

​hr_candidate_portal_accounts

​hr_candidate_portal_sessions

​hr_candidates

​hr_career_paths

​hr_case_documents

​hr_clinical_oversight_relationships

​hr_clinical_oversight_requirements

​hr_clinical_oversight_sessions

​hr_cobra_elections

​hr_cobra_events

​hr_communication_templates

​hr_compensation_analyses

​hr_competency_framework_skills

​hr_competency_frameworks

​hr_contractor_classification_tests

​hr_contractor_contracts

​hr_contractor_credentials

​hr_contractor_time_entries

​hr_contractors

​hr_credential_alerts

​hr_credential_renewal_workflows

​hr_credential_requirements

​hr_credential_types

​hr_deduction_types

​hr_disciplinary_actions

​hr_document_access_logs

​hr_employee_bank_accounts

​hr_employee_credentials

​hr_employee_deductions

​hr_employee_eeo_profiles

​hr_employee_leave_balances

​hr_employee_preferences

​hr_employee_sites

​hr_employee_skills

​hr_employee_tax_info

​hr_employee_teams

​hr_employee_transfers

​hr_employees

​hr_employer_tax_config

​hr_engagement_action_plans

​hr_engagement_analytics

​hr_engagement_surveys

​hr_enrollment_reminder_logs

​hr_entra_sync_log

​hr_entra_sync_pending_changes

​hr_entra_unmatched_users

​hr_everify_audit_events

​hr_everify_cases

​hr_everify_config

​hr_exit_interviews

​hr_federal_tax_bracket_defaults

​hr_federal_tax_brackets

​hr_fica_config

​hr_fingerprint_clearance_incidents

​hr_fingerprint_clearances

​hr_fmla_cases

​hr_grievance_assignees

​hr_grievances

​hr_handbook_bundle_items

Entity relationships

Tables

`hr_aca_compliance`

`hr_aca_employee_coverage`

`hr_analytics_cache`

`hr_applications`

`hr_availability_preferences`

`hr_background_check_providers`

`hr_background_check_webhook_dlq`

`hr_background_check_webhooks`

`hr_background_checks`

`hr_benefits_dependents`

`hr_benefits_eligibility_rules`

`hr_benefits_enrollment_dependents`

`hr_benefits_enrollments`

`hr_benefits_guides`

`hr_benefits_life_events`

`hr_benefits_open_enrollment_periods`

`hr_benefits_plans`

`hr_candidate_communications`

`hr_candidate_eeo_data`

`hr_candidate_portal_accounts`

`hr_candidate_portal_sessions`

`hr_candidates`

`hr_career_paths`

`hr_case_documents`

`hr_clinical_oversight_relationships`

`hr_clinical_oversight_requirements`

`hr_clinical_oversight_sessions`

`hr_cobra_elections`

`hr_cobra_events`

`hr_communication_templates`

`hr_compensation_analyses`

`hr_competency_framework_skills`

`hr_competency_frameworks`

`hr_contractor_classification_tests`

`hr_contractor_contracts`

`hr_contractor_credentials`

`hr_contractor_time_entries`

`hr_contractors`

`hr_credential_alerts`

`hr_credential_renewal_workflows`

`hr_credential_requirements`

`hr_credential_types`

`hr_deduction_types`

`hr_disciplinary_actions`

`hr_document_access_logs`

`hr_employee_bank_accounts`

`hr_employee_credentials`

`hr_employee_deductions`

`hr_employee_eeo_profiles`

`hr_employee_leave_balances`

`hr_employee_preferences`

`hr_employee_sites`

`hr_employee_skills`

`hr_employee_tax_info`

`hr_employee_teams`

`hr_employee_transfers`

`hr_employees`

`hr_employer_tax_config`

`hr_engagement_action_plans`

`hr_engagement_analytics`

`hr_engagement_surveys`

`hr_enrollment_reminder_logs`

`hr_entra_sync_log`

`hr_entra_sync_pending_changes`

`hr_entra_unmatched_users`

`hr_everify_audit_events`

`hr_everify_cases`

`hr_everify_config`

`hr_exit_interviews`

`hr_federal_tax_bracket_defaults`

`hr_federal_tax_brackets`

`hr_fica_config`

`hr_fingerprint_clearance_incidents`

`hr_fingerprint_clearances`

`hr_fmla_cases`

`hr_grievance_assignees`

`hr_grievances`

`hr_handbook_bundle_items`