Encore OS — Documentation

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

Core: it_ · Tables: 37 · Source generated: 2026-06-10T02:55:32.169Z

Entity relationships

Tables

`it_access_accounts`

RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`employee_id`	uuid	no
`account_type`	text	no
`account_identifier`	text	yes
`account_name`	text	yes
`access_level`	text	yes
`status`	text	no	’pending’::text
`provisioned_at`	timestamp with time zone	yes
`provisioned_by`	uuid	yes
`deactivated_at`	timestamp with time zone	yes
`deactivated_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()

Foreign keys:

deactivated_by → pf_profiles(id)
employee_id → hr_employees(id)
organization_id → pf_organizations(id)
provisioned_by → pf_profiles(id)

Policies:

it_access_accounts_delete (DELETE)
it_access_accounts_insert (INSERT)
it_access_accounts_select (SELECT)
it_access_accounts_update (UPDATE)

`it_asset_assignments`

IT-01: Tracks asset assignments to employees. Supports updates for return workflow. RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default	Comment
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`asset_id`	uuid	no
`employee_id`	uuid	yes		Reference to HR employee - no FK to maintain core independence
`assigned_date`	date	no	CURRENT_DATE
`assigned_by`	uuid	no
`assignment_location_site_id`	uuid	yes
`assignment_building`	text	yes
`assignment_room`	text	yes
`assignment_desk`	text	yes
`assignment_notes`	text	yes
`returned_date`	date	yes
`returned_by`	uuid	yes
`return_reason`	text	yes
`return_condition`	text	yes
`is_active`	boolean	no	true	Only one active assignment per asset allowed
`created_at`	timestamp with time zone	no	now()
`custom_fields`	jsonb	no	’{}‘::jsonb	Organization-specific custom metadata for assignments
`updated_at`	timestamp with time zone	no	now()	Timestamp of last update
`updated_by`	uuid	yes		User who last updated this record
`assignee_profile_id`	uuid	yes		Denormalized profile_id from hr_employees to eliminate cross-core dependency in RLS policies

Foreign keys:

asset_id → it_assets(id)
assigned_by → pf_profiles(id)
assignee_profile_id → pf_profiles(id)
assignment_location_site_id → pf_sites(id)
organization_id → pf_organizations(id)
returned_by → pf_profiles(id)
updated_by → pf_profiles(id)

Policies:

it_asset_assignments_org_delete (DELETE)
it_asset_assignments_org_insert (INSERT)
it_asset_assignments_org_select (SELECT)
it_asset_assignments_org_update (UPDATE)

`it_asset_maintenance`

IT-01: Tracks maintenance records for IT assets RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default	Comment
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`asset_id`	uuid	no
`ticket_id`	uuid	yes		Future FK to it_tickets (IT-02)
`maintenance_type`	text	no
`maintenance_date`	date	no	CURRENT_DATE
`maintenance_description`	text	no
`maintenance_cost`	numeric(10,2)	yes	0
`maintenance_vendor_id`	uuid	yes		Future FK to it_vendors (IT-03)
`performed_by`	uuid	yes
`created_at`	timestamp with time zone	no	now()
`created_by`	uuid	no
`custom_fields`	jsonb	no	’{}‘::jsonb	Organization-specific custom metadata for maintenance records
`updated_at`	timestamp with time zone	no	now()	Timestamp of last update
`updated_by`	uuid	yes		User who last updated this record

Foreign keys:

asset_id → it_assets(id)
created_by → pf_profiles(id)
organization_id → pf_organizations(id)
performed_by → pf_profiles(id)
updated_by → pf_profiles(id)

Policies:

it_asset_maintenance_org_delete (DELETE)
it_asset_maintenance_org_insert (INSERT)
it_asset_maintenance_org_select (SELECT)
it_asset_maintenance_org_update (UPDATE)

`it_assets`

IT Asset Management - Core asset records (IT-01) RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default	Comment
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`site_id`	uuid	yes
`asset_tag`	text	no
`asset_name`	text	no
`description`	text	yes
`model`	text	yes
`serial_number`	text	yes
`asset_type`	text	no	’desktop’::text
`mac_address`	text	yes
`ip_address`	text	yes
`operating_system`	text	yes
`os_version`	text	yes
`installed_software`	jsonb	yes	’[]‘::jsonb
`current_building`	text	yes
`current_room`	text	yes
`current_desk`	text	yes
`purchase_date`	date	yes
`purchase_cost`	numeric(12,2)	yes
`purchase_order_id`	uuid	yes
`vendor_id`	uuid	yes
`warranty_period_months`	integer	yes
`warranty_expiration_date`	date	yes
`warranty_vendor_id`	uuid	yes
`warranty_notes`	text	yes
`support_contract_number`	text	yes
`support_contract_vendor_id`	uuid	yes
`support_contract_start_date`	date	yes
`support_contract_expiration_date`	date	yes
`support_contract_notes`	text	yes
`total_maintenance_cost`	numeric(12,2)	yes	0
`total_maintenance_count`	integer	yes	0
`last_maintenance_date`	date	yes
`status`	text	no	’active’::text
`disposed_date`	date	yes
`disposal_method`	text	yes
`disposal_value`	numeric(12,2)	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	no
`updated_by`	uuid	yes
`asset_category`	text	yes		High-level category (end_user_devices, infrastructure, peripherals, communications, software, other)

Foreign keys:

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

Policies:

it_assets_org_delete (DELETE)
it_assets_org_insert (INSERT)
it_assets_org_select (SELECT)
it_assets_org_update (UPDATE)

`it_change_approvals`

RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default
`id`	uuid	no	gen_random_uuid()
`change_request_id`	uuid	no
`organization_id`	uuid	no
`approval_type`	text	no
`step_order`	integer	no	1
`approver_id`	uuid	yes
`approver_role`	text	yes
`status`	text	no	’pending’::text
`decision_at`	timestamp with time zone	yes
`comments`	text	yes
`delegated_to`	uuid	yes
`created_at`	timestamp with time zone	no	now()
`updated_at`	timestamp with time zone	no	now()

Foreign keys:

approver_id → pf_profiles(id)
change_request_id → it_change_requests(id)
delegated_to → pf_profiles(id)
organization_id → pf_organizations(id)

Policies:

it_change_approvals_delete (DELETE)
it_change_approvals_insert (INSERT)
it_change_approvals_select (SELECT)
it_change_approvals_update (UPDATE)

`it_change_blackouts`

RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default	Comment
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`name`	text	no
`start_at`	timestamp with time zone	no
`end_at`	timestamp with time zone	no
`reason`	text	yes
`allow_emergency`	boolean	no	true
`created_at`	timestamp with time zone	no	now()
`created_by`	uuid	yes
`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)
updated_by → users(id)

Policies:

it_change_blackouts_delete (DELETE)
it_change_blackouts_insert (INSERT)
it_change_blackouts_select (SELECT)
it_change_blackouts_update (UPDATE)

`it_change_history`

RLS: ✅ enabled (2 policies)

Column	Type	Nullable	Default
`id`	uuid	no	gen_random_uuid()
`change_request_id`	uuid	no
`organization_id`	uuid	no
`action`	text	no
`old_value`	jsonb	yes
`new_value`	jsonb	yes
`changed_by`	uuid	yes
`changed_at`	timestamp with time zone	no	now()
`notes`	text	yes
`created_at`	timestamp with time zone	yes	now()
`updated_at`	timestamp with time zone	yes	now()

Foreign keys:

change_request_id → it_change_requests(id)
changed_by → pf_profiles(id)
organization_id → pf_organizations(id)

Policies:

it_change_history_insert (INSERT)
it_change_history_select (SELECT)

`it_change_implementations`

RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default	Comment
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`change_request_id`	uuid	no
`title`	text	no
`description`	text	yes
`status`	text	no	’pending’::text
`assigned_to`	uuid	yes
`started_at`	timestamp with time zone	yes
`completed_at`	timestamp with time zone	yes
`notes`	text	yes
`custom_fields`	jsonb	no	’{}‘::jsonb	Organization-specific metadata for change implementation records. Structure varies by organization 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
`deleted_at`	timestamp with time zone	yes
`site_id`	uuid	yes

Foreign keys:

assigned_to → pf_profiles(id)
change_request_id → it_change_requests(id)
created_by → users(id)
organization_id → pf_organizations(id)
site_id → pf_sites(id)
updated_by → users(id)

Policies:

it_change_implementations_delete (DELETE)
it_change_implementations_insert (INSERT)
it_change_implementations_select (SELECT)
it_change_implementations_update (UPDATE)

`it_change_requests`

RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`cr_number`	text	no
`title`	text	no
`description`	text	no
`change_type`	text	no	’normal’::text
`category`	text	no
`status`	text	no	’draft’::text
`impact_score`	integer	yes
`likelihood_score`	integer	yes
`rollback_complexity_score`	integer	yes
`risk_level`	text	yes
`business_justification`	text	yes
`implementation_plan`	text	yes
`rollback_plan`	text	yes
`test_plan`	text	yes
`scheduled_start_at`	timestamp with time zone	yes
`scheduled_end_at`	timestamp with time zone	yes
`actual_start_at`	timestamp with time zone	yes
`actual_end_at`	timestamp with time zone	yes
`requester_id`	uuid	no
`implementer_id`	uuid	yes
`affected_asset_ids`	uuid[]	yes
`related_ticket_id`	uuid	yes
`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 → pf_profiles(id)
implementer_id → pf_profiles(id)
organization_id → pf_organizations(id)
related_ticket_id → it_tickets(id)
requester_id → pf_profiles(id)
template_id → it_change_templates(id)
updated_by → pf_profiles(id)

Policies:

it_change_requests_delete (DELETE)
it_change_requests_insert (INSERT)
it_change_requests_select (SELECT)
it_change_requests_update (UPDATE)

`it_change_tasks`

RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default
`id`	uuid	no	gen_random_uuid()
`change_request_id`	uuid	no
`organization_id`	uuid	no
`title`	text	no
`description`	text	yes
`task_type`	text	no
`status`	text	no	’pending’::text
`assigned_to`	uuid	yes
`due_at`	timestamp with time zone	yes
`started_at`	timestamp with time zone	yes
`completed_at`	timestamp with time zone	yes
`duration_minutes`	integer	yes
`notes`	text	yes
`display_order`	integer	no	0
`created_at`	timestamp with time zone	no	now()
`updated_at`	timestamp with time zone	no	now()

Foreign keys:

assigned_to → pf_profiles(id)
change_request_id → it_change_requests(id)
organization_id → pf_organizations(id)

Policies:

it_change_tasks_delete (DELETE)
it_change_tasks_insert (INSERT)
it_change_tasks_select (SELECT)
it_change_tasks_update (UPDATE)

`it_change_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
`category`	text	no
`is_active`	boolean	no	true
`default_implementation_plan`	text	yes
`default_rollback_plan`	text	yes
`default_test_plan`	text	yes
`default_risk_level`	text	yes	’low’::text
`auto_approve`	boolean	no	true
`template_tasks`	jsonb	yes	’[]‘::jsonb
`usage_count`	integer	no	0
`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)
updated_by → users(id)

Policies:

it_change_templates_delete (DELETE)
it_change_templates_insert (INSERT)
it_change_templates_select (SELECT)
it_change_templates_update (UPDATE)

`it_compliance_requirements`

RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`requirement_name`	text	no
`compliance_framework`	text	no
`requirement_description`	text	yes
`requirement_category`	text	yes
`compliance_status`	text	no	’not_assessed’::text
`last_assessment_date`	date	yes
`next_assessment_date`	date	yes
`assessment_notes`	text	yes
`evidence_document_ids`	uuid[]	yes	’{}‘::uuid[]
`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:

it_compliance_requirements_delete (DELETE)
it_compliance_requirements_insert (INSERT)
it_compliance_requirements_select (SELECT)
it_compliance_requirements_update (UPDATE)

`it_dashboard_preferences`

IT-07: User-specific dashboard layout and preferences RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`user_id`	uuid	no
`widget_layout`	jsonb	yes	’[]‘::jsonb
`default_date_range`	text	yes	’30d’::text
`auto_refresh_enabled`	boolean	yes	true
`auto_refresh_interval_seconds`	integer	yes	30
`hidden_widgets`	text[]	yes	’{}‘::text[]
`custom_fields`	jsonb	yes	’{}‘::jsonb
`created_at`	timestamp with time zone	no	now()
`updated_at`	timestamp with time zone	no	now()

Foreign keys:

organization_id → pf_organizations(id)
user_id → pf_profiles(id)

Policies:

it_dashboard_preferences_delete (DELETE)
it_dashboard_preferences_insert (INSERT)
it_dashboard_preferences_select (SELECT)
it_dashboard_preferences_update (UPDATE)

`it_knowledge_base`

IT-02: Knowledge base articles for self-service and technician reference RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`title`	text	no
`content`	text	no
`summary`	text	yes
`category`	text	no	’general’::text
`tags`	text[]	no	’{}‘::text[]
`status`	text	no	’draft’::text
`published_at`	timestamp with time zone	yes
`view_count`	integer	no	0
`linked_ticket_count`	integer	no	0
`helpful_count`	integer	no	0
`not_helpful_count`	integer	no	0
`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:

it_knowledge_base_delete (DELETE)
it_knowledge_base_insert (INSERT)
it_knowledge_base_select (SELECT)
it_knowledge_base_update (UPDATE)

`it_knowledge_base_versions`

RLS: ✅ enabled (2 policies)

Column	Type	Nullable	Default
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`article_id`	uuid	no
`version`	integer	no
`title`	text	no
`content`	text	no
`category`	text	yes
`tags`	text[]	yes
`created_at`	timestamp with time zone	no	now()
`created_by`	uuid	yes

Foreign keys:

article_id → it_knowledge_base(id)
created_by → pf_profiles(id)
organization_id → pf_organizations(id)

Policies:

it_kb_versions_insert (INSERT)
it_kb_versions_select (SELECT)

`it_license_assignments`

RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`license_id`	uuid	no
`assigned_to_user`	uuid	yes
`assigned_to_asset`	uuid	yes
`assigned_by`	uuid	no
`assigned_at`	timestamp with time zone	no	now()
`revoked_at`	timestamp with time zone	yes
`revoked_by`	uuid	yes
`revocation_reason`	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()

Foreign keys:

assigned_by → pf_profiles(id)
assigned_to_asset → it_assets(id)
assigned_to_user → pf_profiles(id)
license_id → it_licenses(id)
organization_id → pf_organizations(id)
revoked_by → pf_profiles(id)

Policies:

it_license_assignments_delete (DELETE)
it_license_assignments_insert (INSERT)
it_license_assignments_select (SELECT)
it_license_assignments_update (UPDATE)

`it_licenses`

RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default	Comment
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`site_id`	uuid	yes
`license_key`	text	yes
`name`	text	no
`description`	text	yes
`software_name`	text	no
`software_version`	text	yes
`publisher`	text	yes
`license_type`	text	no	’subscription’::text
`status`	text	no	’active’::text
`total_seats`	integer	no	1
`assigned_seats`	integer	no	0
`purchase_date`	date	yes
`activation_date`	date	yes
`expiry_date`	date	yes
`purchase_cost`	numeric(12,2)	yes
`renewal_cost`	numeric(12,2)	yes
`cost_per_seat`	numeric(12,2)	yes
`currency`	text	yes	’USD’::text
`vendor_id`	uuid	yes
`contract_id`	uuid	yes
`compliance_status`	text	no	’compliant’::text
`renewal_reminder_days`	integer	yes	30
`last_reminder_sent_at`	timestamp with time zone	yes
`custom_fields`	jsonb	no	’{}‘::jsonb	Org-specific extensibility: {“maintenance_agreement”: bool, “support_tier”: string}
`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)
site_id → pf_sites(id)
updated_by → pf_profiles(id)

Policies:

it_licenses_delete (DELETE)
it_licenses_insert (INSERT)
it_licenses_select (SELECT)
it_licenses_update (UPDATE)

`it_module_settings`

Organization-level configuration for IT module (IT-01 through IT-09) RLS: ✅ enabled (2 policies)

Column	Type	Nullable	Default	Comment
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`asset_tag_prefix`	text	yes	’ASSET’::text	Prefix for auto-generated asset tags
`asset_tag_sequence`	integer	yes	1
`asset_depreciation_method`	text	yes	’straight_line’::text
`enable_qr_codes`	boolean	yes	true
`require_assignment_approval`	boolean	yes	false
`auto_retirement_enabled`	boolean	yes	false
`auto_retirement_days`	integer	yes	1825
`ticket_number_prefix`	text	yes	’TKT’::text	Prefix for auto-generated ticket numbers
`ticket_number_sequence`	integer	yes	1
`default_sla_response_hours`	integer	yes	4
`default_sla_resolution_hours`	integer	yes	24
`enable_sla_escalation`	boolean	yes	true
`business_hours_start`	time without time zone	yes	’08:00:00’::time without time zone
`business_hours_end`	time without time zone	yes	’17:00:00’::time without time zone
`business_days`	integer[]	yes	ARRAY[1, 2, 3, 4, 5]	Array of ISO weekday numbers (1=Monday, 7=Sunday) for SLA calculations
`contract_alert_days`	integer[]	yes	ARRAY[90, 60, 30]
`vendor_rating_enabled`	boolean	yes	true
`require_vendor_insurance`	boolean	yes	false
`contract_number_prefix`	text	yes	’CTR’::text
`contract_number_sequence`	integer	yes	1
`license_alert_days`	integer[]	yes	ARRAY[60, 30, 14]
`compliance_check_frequency`	text	yes	’weekly’::text
`true_up_reminder_days`	integer	yes	30
`license_harvesting_enabled`	boolean	yes	false
`harvesting_inactivity_days`	integer	yes	90
`critical_vuln_alert_days`	integer	yes	1
`high_vuln_alert_days`	integer	yes	7
`vulnerability_scan_frequency`	text	yes	’weekly’::text
`patch_compliance_target_percent`	integer	yes	95
`incident_number_prefix`	text	yes	’INC’::text
`incident_number_sequence`	integer	yes	1
`purchase_request_prefix`	text	yes	’PR’::text
`purchase_request_sequence`	integer	yes	1
`purchase_order_prefix`	text	yes	’PO’::text
`purchase_order_sequence`	integer	yes	1
`approval_threshold_low`	numeric(12,2)	yes	500.00
`approval_threshold_medium`	numeric(12,2)	yes	5000.00
`approval_threshold_high`	numeric(12,2)	yes	25000.00
`require_three_quotes_above`	numeric(12,2)	yes	10000.00
`dashboard_refresh_interval_seconds`	integer	yes	300
`kpi_targets`	jsonb	yes	’{}‘::jsonb	JSON object with KPI target values for dashboard widgets
`widget_preferences`	jsonb	yes	’{}‘::jsonb
`provisioning_sla_hours`	integer	yes	24
`deprovisioning_sla_hours`	integer	yes	4
`auto_create_it_onboarding`	boolean	yes	true
`it_onboarding_template_id`	uuid	yes
`default_new_hire_equipment`	jsonb	yes	’[]‘::jsonb
`offboarding_checklist_template_id`	uuid	yes
`change_request_prefix`	text	yes	’CHG’::text
`change_request_sequence`	integer	yes	1
`cab_required_threshold`	text	yes	’high’::text
`change_freeze_periods`	jsonb	yes	’[]‘::jsonb	JSON array of {start_date, end_date, reason} objects
`require_rollback_plan`	boolean	yes	true
`auto_notify_stakeholders`	boolean	yes	true
`change_lead_time_days`	integer	yes	5
`emergency_change_requires_approval`	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
`kb_suggestion_limit`	integer	yes	5	Maximum number of suggested knowledge base articles shown when creating a ticket
`use_configurable_wizards`	boolean	yes	false	PF-41: Use configurable wizard templates when true; otherwise use hardcoded wizards.

Foreign keys:

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

Policies:

it_module_settings_insert (INSERT)
it_module_settings_update (UPDATE)

`it_onboarding_instances`

RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default	Comment
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`employee_id`	uuid	no
`template_id`	uuid	yes
`hr_onboarding_instance_id`	uuid	yes
`workflow_type`	text	no
`status`	text	no	’pending’::text
`priority`	text	no	’normal’::text
`target_date`	date	no
`started_at`	timestamp with time zone	yes
`completed_at`	timestamp with time zone	yes
`assigned_to`	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		User who last updated this record

Foreign keys:

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

Policies:

it_onboarding_instances_delete (DELETE)
it_onboarding_instances_insert (INSERT)
it_onboarding_instances_select (SELECT)
it_onboarding_instances_update (UPDATE)

`it_onboarding_task_instances`

RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default
`id`	uuid	no	gen_random_uuid()
`onboarding_instance_id`	uuid	no
`task_id`	uuid	yes
`hr_task_instance_id`	uuid	yes
`title`	text	no
`description`	text	yes
`category`	text	no	’other’::text
`status`	text	no	’pending’::text
`is_required`	boolean	no	true
`due_date`	date	yes
`completed_at`	timestamp with time zone	yes
`completed_by`	uuid	yes
`asset_id`	uuid	yes
`license_assignment_id`	uuid	yes
`ticket_id`	uuid	yes
`notes`	text	yes
`completion_notes`	text	yes
`created_at`	timestamp with time zone	no	now()
`updated_at`	timestamp with time zone	no	now()
`organization_id`	uuid	yes

Foreign keys:

completed_by → pf_profiles(id)
onboarding_instance_id → it_onboarding_instances(id)
organization_id → pf_organizations(id)
task_id → it_onboarding_tasks(id)
ticket_id → it_tickets(id)

Policies:

it_onboarding_task_instances_delete (DELETE)
it_onboarding_task_instances_insert (INSERT)
it_onboarding_task_instances_select (SELECT)
it_onboarding_task_instances_update (UPDATE)

`it_onboarding_tasks`

RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default
`id`	uuid	no	gen_random_uuid()
`template_id`	uuid	no
`title`	text	no
`description`	text	yes
`category`	text	no	’other’::text
`is_required`	boolean	no	true
`due_days_offset`	integer	no	0
`display_order`	integer	no	0
`automation_type`	text	yes	’manual’::text
`external_system`	text	yes
`automation_config`	jsonb	yes	’{}‘::jsonb
`created_at`	timestamp with time zone	no	now()
`updated_at`	timestamp with time zone	yes	now()
`organization_id`	uuid	yes

Foreign keys:

organization_id → pf_organizations(id)
template_id → it_onboarding_templates(id)

Policies:

it_onboarding_tasks_delete (DELETE)
it_onboarding_tasks_insert (INSERT)
it_onboarding_tasks_select (SELECT)
it_onboarding_tasks_update (UPDATE)

`it_onboarding_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
`workflow_type`	text	no	’onboarding’::text
`role_type`	text	yes
`is_active`	boolean	no	true
`is_default`	boolean	no	false
`standard_equipment`	jsonb	yes	’[]‘::jsonb
`standard_software`	jsonb	yes	’[]‘::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)
organization_id → pf_organizations(id)
updated_by → pf_profiles(id)

Policies:

it_onboarding_templates_delete (DELETE)
it_onboarding_templates_insert (INSERT)
it_onboarding_templates_select (SELECT)
it_onboarding_templates_update (UPDATE)

`it_patch_deployments`

RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`patch_id`	uuid	no
`asset_id`	uuid	no
`deployment_status`	text	no	’pending’::text
`deployed_date`	date	yes
`deployed_by`	uuid	yes
`deployment_notes`	text	yes
`created_at`	timestamp with time zone	no	now()
`updated_at`	timestamp with time zone	no	now()

Foreign keys:

asset_id → it_assets(id)
deployed_by → pf_profiles(id)
organization_id → pf_organizations(id)
patch_id → it_security_patches(id)

Policies:

it_patch_deployments_delete (DELETE)
it_patch_deployments_insert (INSERT)
it_patch_deployments_select (SELECT)
it_patch_deployments_update (UPDATE)

`it_purchase_request_items`

RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`request_id`	uuid	no
`item_name`	text	no
`item_description`	text	yes
`quantity`	integer	no	1
`unit_cost`	numeric(12,2)	yes
`total_cost`	numeric(12,2)	yes
`asset_type`	text	yes
`vendor_id`	uuid	yes
`sort_order`	integer	yes	0
`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)
request_id → it_purchase_requests(id)
vendor_id → it_vendors(id)

Policies:

it_purchase_request_items_delete (DELETE)
it_purchase_request_items_insert (INSERT)
it_purchase_request_items_select (SELECT)
it_purchase_request_items_update (UPDATE)

`it_purchase_requests`

RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`site_id`	uuid	yes
`request_number`	text	no
`request_type`	text	no
`title`	text	no
`description`	text	no
`quantity`	integer	no	1
`unit_cost`	numeric(12,2)	yes
`total_cost`	numeric(12,2)	yes
`vendor_id`	uuid	yes
`asset_type`	text	yes
`justification`	text	yes
`budget_code`	text	yes
`priority`	text	no	’normal’::text
`needed_by_date`	date	yes
`approval_status`	text	no	’draft’::text
`submitted_at`	timestamp with time zone	yes
`submitted_by`	uuid	yes
`approved_by`	uuid	yes
`approved_at`	timestamp with time zone	yes
`rejection_reason`	text	yes
`purchase_order_id`	uuid	yes
`purchase_order_number`	text	yes
`received_at`	timestamp with time zone	yes
`received_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

Foreign keys:

approved_by → pf_profiles(id)
created_by → pf_profiles(id)
organization_id → pf_organizations(id)
received_by → pf_profiles(id)
site_id → pf_sites(id)
submitted_by → pf_profiles(id)
updated_by → pf_profiles(id)
vendor_id → it_vendors(id)

Policies:

it_purchase_requests_delete (DELETE)
it_purchase_requests_insert (INSERT)
it_purchase_requests_select (SELECT)
it_purchase_requests_update (UPDATE)

`it_report_definitions`

IT-07: Saved report configurations and 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
`report_type`	text	no
`filters`	jsonb	yes	’{}‘::jsonb
`columns`	jsonb	yes	’[]‘::jsonb
`grouping`	jsonb	yes	’[]‘::jsonb
`sorting`	jsonb	yes	’[]‘::jsonb
`schedule_enabled`	boolean	yes	false
`schedule_cron`	text	yes
`schedule_recipients`	jsonb	yes	’[]‘::jsonb
`last_run_at`	timestamp with time zone	yes
`next_run_at`	timestamp with time zone	yes
`is_template`	boolean	yes	false
`is_public`	boolean	yes	false
`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

Foreign keys:

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

Policies:

it_report_definitions_delete (DELETE)
it_report_definitions_insert (INSERT)
it_report_definitions_select (SELECT)
it_report_definitions_update (UPDATE)

`it_report_runs`

IT-07: Report execution history and generated files RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default	Comment
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`report_definition_id`	uuid	yes
`status`	text	no	’pending’::text
`run_type`	text	no
`report_name`	text	no
`parameters`	jsonb	yes	’{}‘::jsonb
`row_count`	integer	yes
`file_url`	text	yes
`file_format`	text	yes
`file_size_bytes`	bigint	yes
`error_message`	text	yes
`started_at`	timestamp with time zone	yes
`completed_at`	timestamp with time zone	yes
`custom_fields`	jsonb	yes	’{}‘::jsonb
`created_at`	timestamp with time zone	no	now()
`created_by`	uuid	yes
`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)
report_definition_id → it_report_definitions(id)
updated_by → users(id)

Policies:

it_report_runs_delete (DELETE)
it_report_runs_insert (INSERT)
it_report_runs_select (SELECT)
it_report_runs_update (UPDATE)

`it_security_incidents`

RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`ticket_id`	uuid	yes
`incident_type`	text	no
`severity`	text	no
`title`	text	no
`description`	text	no
`discovered_date`	date	no
`response_status`	text	no	’open’::text
`resolved_date`	date	yes
`resolution_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)
ticket_id → it_tickets(id)
updated_by → pf_profiles(id)

Policies:

it_security_incidents_delete (DELETE)
it_security_incidents_insert (INSERT)
it_security_incidents_select (SELECT)
it_security_incidents_update (UPDATE)

`it_security_patches`

RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`patch_id`	text	no
`patch_name`	text	no
`description`	text	yes
`severity`	text	no
`release_date`	date	no
`affected_software`	text	yes
`vendor_id`	uuid	yes
`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)
organization_id → pf_organizations(id)
updated_by → pf_profiles(id)
vendor_id → it_vendors(id)

Policies:

it_security_patches_delete (DELETE)
it_security_patches_insert (INSERT)
it_security_patches_select (SELECT)
it_security_patches_update (UPDATE)

`it_sla_policies`

Configurable SLA policies per priority level for IT tickets. 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
`priority`	text	no
`response_time_minutes`	integer	no		Target time for first response in minutes.
`resolution_time_minutes`	integer	no		Target time for resolution in minutes.
`business_hours_only`	boolean	no	true	If true, SLA clock only runs during business hours.
`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:

it_sla_policies_delete (DELETE)
it_sla_policies_insert (INSERT)
it_sla_policies_select (SELECT)
it_sla_policies_update (UPDATE)

`it_ticket_attachments`

Junction table linking IT tickets to PF-11 documents. Append-only. RLS: ✅ enabled (2 policies)

Column	Type	Nullable	Default	Comment
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`ticket_id`	uuid	no
`document_id`	uuid	no
`attachment_type`	text	no	’document’::text	Type of attachment: screenshot, log_file, document, other
`description`	text	yes
`custom_fields`	jsonb	no	’{}‘::jsonb
`created_at`	timestamp with time zone	no	now()
`created_by`	uuid	yes
`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)
document_id → pf_documents(id)
organization_id → pf_organizations(id)
ticket_id → it_tickets(id)
updated_by → users(id)

Policies:

it_ticket_attachments_insert (INSERT)
it_ticket_attachments_select (SELECT)

`it_ticket_comments`

IT-02: Ticket conversation history. Append-only (no updates/deletes). RLS: ✅ enabled (2 policies)

Column	Type	Nullable	Default	Comment
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`ticket_id`	uuid	no
`comment_text`	text	no
`is_internal`	boolean	no	false	Internal notes visible only to IT staff, not the requester.
`author_id`	uuid	no
`custom_fields`	jsonb	no	’{}‘::jsonb
`created_at`	timestamp with time zone	no	now()
`updated_at`	timestamp with time zone	yes	now()

Foreign keys:

author_id → pf_profiles(id)
organization_id → pf_organizations(id)
ticket_id → it_tickets(id)

Policies:

it_ticket_comments_insert (INSERT)
it_ticket_comments_select (SELECT)

`it_tickets`

IT-02: Service desk tickets for incidents, requests, problems, and changes RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default	Comment
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`site_id`	uuid	yes
`ticket_number`	text	no
`ticket_type`	text	no	’request’::text	Ticket type: request, incident, problem, change
`category`	text	no	’other’::text
`priority`	text	no	’medium’::text	Priority: critical, high, medium, low
`subject`	text	no
`description`	text	no
`resolution_notes`	text	yes
`requested_by`	uuid	no
`assigned_to_profile_id`	uuid	yes
`assigned_at`	timestamp with time zone	yes
`linked_asset_id`	uuid	yes
`sla_target_hours`	integer	yes
`sla_start_time`	timestamp with time zone	yes
`sla_due_time`	timestamp with time zone	yes
`sla_breached`	boolean	yes	false
`status`	text	no	’open’::text	Status: open, assigned, in_progress, on_hold, resolved, closed, cancelled
`resolved_at`	timestamp with time zone	yes
`closed_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
`knowledge_base_article_id`	uuid	yes

Foreign keys:

assigned_to_profile_id → pf_profiles(id)
created_by → pf_profiles(id)
knowledge_base_article_id → it_knowledge_base(id)
linked_asset_id → it_assets(id)
organization_id → pf_organizations(id)
requested_by → pf_profiles(id)
site_id → pf_sites(id)
updated_by → pf_profiles(id)

Policies:

it_tickets_delete (DELETE)
it_tickets_insert (INSERT)
it_tickets_select (SELECT)
it_tickets_update (UPDATE)

`it_vendor_contracts`

RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`vendor_id`	uuid	no
`contract_number`	text	no
`contract_name`	text	no
`contract_type`	text	no
`contract_description`	text	yes
`start_date`	date	no
`expiration_date`	date	yes
`renewal_date`	date	yes
`auto_renew`	boolean	yes	false
`contract_value`	numeric(12,2)	yes
`payment_terms`	text	yes
`status`	text	no	’active’::text
`document_url`	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)
vendor_id → it_vendors(id)

Policies:

it_vendor_contracts_delete (DELETE)
it_vendor_contracts_insert (INSERT)
it_vendor_contracts_select (SELECT)
it_vendor_contracts_update (UPDATE)

`it_vendors`

RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`vendor_name`	text	no
`vendor_type`	text	no
`status`	text	no	’active’::text
`contact_name`	text	yes
`contact_email`	text	yes
`contact_phone`	text	yes
`website`	text	yes
`address_line1`	text	yes
`address_line2`	text	yes
`city`	text	yes
`state`	text	yes
`postal_code`	text	yes
`country`	text	yes	’USA’::text
`average_response_time_hours`	numeric(5,2)	yes
`satisfaction_rating`	numeric(3,2)	yes
`total_contracts_count`	integer	yes	0
`active_contracts_count`	integer	yes	0
`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:

it_vendors_delete (DELETE)
it_vendors_insert (INSERT)
it_vendors_select (SELECT)
it_vendors_update (UPDATE)

`it_vulnerabilities`

RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`cve_id`	text	yes
`vulnerability_name`	text	no
`description`	text	yes
`severity`	text	no
`discovered_date`	date	no
`affected_asset_ids`	uuid[]	yes	’{}‘::uuid[]
`remediation_status`	text	no	’open’::text
`remediation_date`	date	yes
`remediation_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:

it_vulnerabilities_delete (DELETE)
it_vulnerabilities_insert (INSERT)
it_vulnerabilities_select (SELECT)
it_vulnerabilities_update (UPDATE)

`it_vulnerability_assets`

Junction table linking IT vulnerabilities to affected assets RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default	Comment
`id`	uuid	no	gen_random_uuid()
`vulnerability_id`	uuid	no
`asset_id`	uuid	no
`organization_id`	uuid	no
`notes`	text	yes
`created_at`	timestamp with time zone	no	now()
`created_by`	uuid	yes
`updated_by`	uuid	yes		User who last updated this record
`updated_at`	timestamp with time zone	yes	now()

Foreign keys:

asset_id → it_assets(id)
created_by → pf_profiles(id)
organization_id → pf_organizations(id)
updated_by → users(id)
vulnerability_id → it_vulnerabilities(id)

Policies:

it_vulnerability_assets_delete (DELETE)
it_vulnerability_assets_insert (INSERT)
it_vulnerability_assets_select (SELECT)
it_vulnerability_assets_update (UPDATE)

​Entity relationships

​Tables

​it_access_accounts

​it_asset_assignments

​it_asset_maintenance

​it_assets

​it_change_approvals

​it_change_blackouts

​it_change_history

​it_change_implementations

​it_change_requests

​it_change_tasks

​it_change_templates

​it_compliance_requirements

​it_dashboard_preferences

​it_knowledge_base

​it_knowledge_base_versions

​it_license_assignments

​it_licenses

​it_module_settings

​it_onboarding_instances

​it_onboarding_task_instances

​it_onboarding_tasks

​it_onboarding_templates

​it_patch_deployments

​it_purchase_request_items

​it_purchase_requests

​it_report_definitions

​it_report_runs

​it_security_incidents

​it_security_patches

​it_sla_policies

​it_ticket_attachments

​it_ticket_comments

​it_tickets

​it_vendor_contracts

​it_vendors

​it_vulnerabilities

​it_vulnerability_assets

Entity relationships

Tables

`it_access_accounts`

`it_asset_assignments`

`it_asset_maintenance`

`it_assets`

`it_change_approvals`

`it_change_blackouts`

`it_change_history`

`it_change_implementations`

`it_change_requests`

`it_change_tasks`

`it_change_templates`

`it_compliance_requirements`

`it_dashboard_preferences`

`it_knowledge_base`

`it_knowledge_base_versions`

`it_license_assignments`

`it_licenses`

`it_module_settings`

`it_onboarding_instances`

`it_onboarding_task_instances`

`it_onboarding_tasks`

`it_onboarding_templates`

`it_patch_deployments`

`it_purchase_request_items`

`it_purchase_requests`

`it_report_definitions`

`it_report_runs`

`it_security_incidents`

`it_security_patches`

`it_sla_policies`

`it_ticket_attachments`

`it_ticket_comments`

`it_tickets`

`it_vendor_contracts`

`it_vendors`

`it_vulnerabilities`

`it_vulnerability_assets`