Encore OS — Documentation

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

Core: gr_ · Tables: 82 · Source generated: 2026-06-10T02:55:32.169Z

Entity relationships

Tables

`gr_accreditation_audit_links`

RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`survey_id`	uuid	no
`audit_id`	uuid	no
`linked_at`	timestamp with time zone	no	now()
`linked_by`	uuid	yes
`notes`	text	yes
`created_at`	timestamp with time zone	yes	now()
`updated_at`	timestamp with time zone	yes	now()

Foreign keys:

audit_id → gr_audits(id)
linked_by → pf_profiles(id)
organization_id → pf_organizations(id)
survey_id → gr_accreditation_surveys(id)

Policies:

gr_accreditation_audit_links_delete (DELETE)
gr_accreditation_audit_links_insert (INSERT)
gr_accreditation_audit_links_select (SELECT)
gr_accreditation_audit_links_update (UPDATE)

`gr_accreditation_caps`

GR-16: Corrective Action Plans linked to accreditation findings RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default	Comment
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`accreditation_id`	uuid	no
`standard_id`	uuid	yes
`cap_title`	text	no
`finding_description`	text	no
`plan_description`	text	yes
`responsible_party_id`	uuid	yes
`target_closure_date`	date	no
`status`	text	no	’identified’::text
`closed_at`	timestamp with time zone	yes
`closure_evidence_id`	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

Foreign keys:

accreditation_id → gr_accreditations(id)
closure_evidence_id → pf_documents(id)
created_by → pf_profiles(id)
organization_id → pf_organizations(id)
responsible_party_id → pf_profiles(id)
standard_id → gr_accreditation_standards(id)
updated_by → pf_profiles(id)

Policies:

gr_caps_delete (DELETE)
gr_caps_insert (INSERT)
gr_caps_select (SELECT)
gr_caps_update (UPDATE)

`gr_accreditation_compliance_links`

RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`standard_id`	uuid	no
`requirement_id`	uuid	no
`linked_at`	timestamp with time zone	no	now()
`linked_by`	uuid	yes
`notes`	text	yes
`created_at`	timestamp with time zone	yes	now()
`updated_at`	timestamp with time zone	yes	now()

Foreign keys:

linked_by → pf_profiles(id)
organization_id → pf_organizations(id)
requirement_id → gr_regulatory_requirements(id)
standard_id → gr_accreditation_standards(id)

Policies:

gr_accreditation_compliance_links_delete (DELETE)
gr_accreditation_compliance_links_insert (INSERT)
gr_accreditation_compliance_links_select (SELECT)
gr_accreditation_compliance_links_update (UPDATE)

`gr_accreditation_evidence`

GR-08: Evidence documents linked to accreditation standards and surveys RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default	Comment
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`standard_id`	uuid	yes
`survey_id`	uuid	yes
`document_id`	uuid	yes
`evidence_type`	text	no
`title`	text	no
`description`	text	yes
`external_url`	text	yes
`file_name`	text	yes
`file_path`	text	yes
`verified_at`	timestamp with time zone	yes
`verified_by`	uuid	yes
`verification_status`	text	yes	’pending’::text
`verification_notes`	text	yes
`custom_fields`	jsonb	no	’{}‘::jsonb	Organization-specific metadata (e.g., {“evidence_category”: “Policy”, “retention_period”: “7 years”})
`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)
document_id → pf_documents(id)
organization_id → pf_organizations(id)
standard_id → gr_accreditation_standards(id)
survey_id → gr_accreditation_surveys(id)
updated_by → pf_profiles(id)
verified_by → pf_profiles(id)

Policies:

gr_accreditation_evidence_delete (DELETE)
gr_accreditation_evidence_insert (INSERT)
gr_accreditation_evidence_select (SELECT)
gr_accreditation_evidence_update (UPDATE)

`gr_accreditation_mock_surveys`

GR-16: Mock survey results for accreditation readiness tracking RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default	Comment
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`accreditation_id`	uuid	no
`survey_date`	date	no
`survey_type`	text	no	’internal’::text
`conducted_by`	uuid	yes
`overall_readiness_score`	numeric(5,2)	yes
`findings`	jsonb	no	’[]‘::jsonb
`recommendations`	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:

accreditation_id → gr_accreditations(id)
conducted_by → pf_profiles(id)
created_by → pf_profiles(id)
organization_id → pf_organizations(id)
updated_by → pf_profiles(id)

Policies:

gr_mock_surveys_delete (DELETE)
gr_mock_surveys_insert (INSERT)
gr_mock_surveys_select (SELECT)
gr_mock_surveys_update (UPDATE)

`gr_accreditation_standards`

GR-08: Accreditation standards with compliance tracking RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default	Comment
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`accreditation_id`	uuid	no
`standard_number`	text	no
`title`	text	no
`description`	text	yes
`category`	text	no
`compliance_status`	text	no	’pending’::text
`last_verified_at`	timestamp with time zone	yes
`last_verified_by`	uuid	yes
`verification_notes`	text	yes
`priority`	text	yes	’medium’::text
`custom_fields`	jsonb	no	’{}‘::jsonb	Organization-specific metadata (e.g., {“regulatory_reference”: “CFR 42.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:

accreditation_id → gr_accreditations(id)
created_by → pf_profiles(id)
last_verified_by → pf_profiles(id)
organization_id → pf_organizations(id)
updated_by → pf_profiles(id)

Policies:

gr_accreditation_standards_delete (DELETE)
gr_accreditation_standards_insert (INSERT)
gr_accreditation_standards_select (SELECT)
gr_accreditation_standards_update (UPDATE)

`gr_accreditation_surveys`

GR-08: Accreditation survey planning and tracking RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default	Comment
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`accreditation_id`	uuid	no
`site_id`	uuid	yes
`survey_type`	text	no
`scope`	text	yes	’organization_wide’::text
`scheduled_date`	date	yes
`scheduled_end_date`	date	yes
`actual_start_date`	date	yes
`actual_end_date`	date	yes
`status`	text	no	’planned’::text
`readiness_score`	numeric(5,2)	yes
`findings_count`	integer	yes	0
`critical_findings_count`	integer	yes	0
`result`	text	yes
`result_notes`	text	yes
`lead_surveyor_name`	text	yes
`surveyor_names`	text[]	yes
`custom_fields`	jsonb	no	’{}‘::jsonb	Organization-specific metadata (e.g., {“preparation_notes”: ”…”, “focus_areas”: […]})
`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:

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

Policies:

gr_accreditation_surveys_delete (DELETE)
gr_accreditation_surveys_insert (INSERT)
gr_accreditation_surveys_select (SELECT)
gr_accreditation_surveys_update (UPDATE)

`gr_accreditation_tracer_packs`

GR-16: Tracer packs grouping evidence by accreditation standard domain/category RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default	Comment
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`accreditation_id`	uuid	no
`title`	text	no
`domain`	text	no		Maps to gr_accreditation_standards.category — the accreditation domain this pack covers
`generated_at`	timestamp with time zone	no	now()
`generated_by`	uuid	yes
`status`	text	no	’draft’::text
`document_id`	uuid	yes
`coverage_pct`	numeric(5,2)	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:

accreditation_id → gr_accreditations(id)
created_by → pf_profiles(id)
document_id → pf_documents(id)
generated_by → pf_profiles(id)
organization_id → pf_organizations(id)
updated_by → pf_profiles(id)

Policies:

gr_tracer_packs_delete (DELETE)
gr_tracer_packs_insert (INSERT)
gr_tracer_packs_select (SELECT)
gr_tracer_packs_update (UPDATE)

`gr_accreditations`

GR-08: Accreditation tracking for regulatory bodies (CARF, state licensing, NARR) RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default	Comment
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`site_id`	uuid	yes
`accreditation_body`	text	no
`accreditation_type`	text	yes
`accreditation_number`	text	yes
`awarded_date`	date	yes
`expires_date`	date	yes
`renewal_due_date`	date	yes
`status`	text	no	’active’::text
`contact_name`	text	yes
`contact_email`	text	yes
`contact_phone`	text	yes
`description`	text	yes
`notes`	text	yes
`custom_fields`	jsonb	no	’{}‘::jsonb	Organization-specific metadata (e.g., {“accreditation_level”: “Three-Year”, “survey_type”: “Full Survey”})
`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)
site_id → pf_sites(id)
updated_by → pf_profiles(id)

Policies:

gr_accreditations_delete (DELETE)
gr_accreditations_insert (INSERT)
gr_accreditations_select (SELECT)
gr_accreditations_update (UPDATE)

`gr_ai_audit_prep`

RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`audit_id`	uuid	yes
`readiness_score`	numeric(5,2)	yes
`readiness_assessment`	text	yes
`gap_areas`	text[]	yes
`potential_findings`	text[]	yes
`checklist_items`	jsonb	yes
`evidence_items`	jsonb	yes
`ai_model`	text	yes
`generated_at`	timestamp with time zone	no	now()
`status`	text	no	’pending’::text
`reviewed_at`	timestamp with time zone	yes
`reviewed_by`	uuid	yes
`custom_fields`	jsonb	no	’{}‘::jsonb
`created_at`	timestamp with time zone	no	now()
`updated_at`	timestamp with time zone	no	now()

Foreign keys:

audit_id → gr_audits(id)
organization_id → pf_organizations(id)
reviewed_by → pf_profiles(id)

Policies:

gr_ai_audit_prep_delete (DELETE)
gr_ai_audit_prep_insert (INSERT)
gr_ai_audit_prep_select (SELECT)
gr_ai_audit_prep_update (UPDATE)

`gr_ai_compliance_suggestions`

RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`suggestion_type`	text	no
`title`	text	no
`description`	text	yes
`priority`	text	yes
`requirement_id`	uuid	yes
`check_id`	uuid	yes
`finding_id`	uuid	yes
`ai_model`	text	yes
`ai_confidence`	numeric(5,2)	yes
`generated_at`	timestamp with time zone	no	now()
`status`	text	no	’pending’::text
`reviewed_at`	timestamp with time zone	yes
`reviewed_by`	uuid	yes
`custom_fields`	jsonb	no	’{}‘::jsonb
`created_at`	timestamp with time zone	no	now()
`updated_at`	timestamp with time zone	no	now()

Foreign keys:

check_id → gr_compliance_checks(id)
finding_id → gr_audit_findings(id)
organization_id → pf_organizations(id)
requirement_id → gr_regulatory_requirements(id)
reviewed_by → pf_profiles(id)

Policies:

gr_ai_suggestions_delete (DELETE)
gr_ai_suggestions_insert (INSERT)
gr_ai_suggestions_select (SELECT)
gr_ai_suggestions_update (UPDATE)

`gr_ai_gap_analyses`

RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`requirement_id`	uuid	yes
`gap_type`	text	no
`gap_description`	text	no
`gap_priority`	text	yes
`recommendation`	text	yes
`suggested_actions`	text[]	yes
`ai_model`	text	yes
`ai_confidence`	numeric(5,2)	yes
`generated_at`	timestamp with time zone	no	now()
`status`	text	no	’pending’::text
`addressed_at`	timestamp with time zone	yes
`addressed_by`	uuid	yes
`custom_fields`	jsonb	no	’{}‘::jsonb
`created_at`	timestamp with time zone	no	now()
`updated_at`	timestamp with time zone	no	now()

Foreign keys:

addressed_by → pf_profiles(id)
organization_id → pf_organizations(id)
requirement_id → gr_regulatory_requirements(id)

Policies:

gr_ai_gap_delete (DELETE)
gr_ai_gap_insert (INSERT)
gr_ai_gap_select (SELECT)
gr_ai_gap_update (UPDATE)

`gr_ai_risk_assessments`

RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`risk_id`	uuid	yes
`suggested_likelihood`	integer	yes
`suggested_impact`	integer	yes
`suggested_risk_score`	integer	yes
`suggested_risk_rating`	text	yes
`assessment_reasoning`	text	yes
`suggested_strategy`	text	yes
`suggested_actions`	text[]	yes
`ai_model`	text	yes
`ai_confidence`	numeric(5,2)	yes
`generated_at`	timestamp with time zone	no	now()
`status`	text	no	’pending’::text
`reviewed_at`	timestamp with time zone	yes
`reviewed_by`	uuid	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)
reviewed_by → pf_profiles(id)
risk_id → gr_risks(id)

Policies:

gr_ai_risk_delete (DELETE)
gr_ai_risk_insert (INSERT)
gr_ai_risk_select (SELECT)
gr_ai_risk_update (UPDATE)

`gr_audit_checklists`

RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default
`id`	uuid	no	gen_random_uuid()
`audit_id`	uuid	no
`requirement_id`	uuid	yes
`item_text`	text	no
`item_order`	integer	no
`is_required`	boolean	yes	true
`reviewed`	boolean	yes	false
`reviewed_by`	uuid	yes
`reviewed_at`	timestamp with time zone	yes
`review_notes`	text	yes
`evidence_present`	boolean	yes	false
`evidence_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:

audit_id → gr_audits(id)
requirement_id → gr_regulatory_requirements(id)
reviewed_by → pf_profiles(id)

Policies:

gr_audit_checklists_delete (DELETE)
gr_audit_checklists_insert (INSERT)
gr_audit_checklists_select (SELECT)
gr_audit_checklists_update (UPDATE)

`gr_audit_corrective_actions`

RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`finding_id`	uuid	no
`action_description`	text	no
`responsible_party_id`	uuid	yes
`due_date`	date	no
`status`	text	no	’planned’::text
`completed_at`	timestamp with time zone	yes
`completed_by`	uuid	yes
`completion_evidence_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

Foreign keys:

completed_by → pf_profiles(id)
completion_evidence_id → gr_audit_evidence(id)
created_by → pf_profiles(id)
finding_id → gr_audit_findings(id)
organization_id → pf_organizations(id)
responsible_party_id → pf_profiles(id)
updated_by → pf_profiles(id)

Policies:

gr_audit_corrective_actions_delete (DELETE)
gr_audit_corrective_actions_insert (INSERT)
gr_audit_corrective_actions_select (SELECT)
gr_audit_corrective_actions_update (UPDATE)

`gr_audit_evidence`

RLS: ✅ enabled (3 policies)

Column	Type	Nullable	Default	Comment
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`audit_id`	uuid	no
`checklist_item_id`	uuid	yes
`finding_id`	uuid	yes
`document_id`	uuid	yes
`evidence_type`	text	yes
`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:

audit_id → gr_audits(id)
checklist_item_id → gr_audit_checklists(id)
created_by → pf_profiles(id)
document_id → pf_documents(id)
finding_id → gr_audit_findings(id)
organization_id → pf_organizations(id)
updated_by → users(id)

Policies:

gr_audit_evidence_delete (DELETE)
gr_audit_evidence_insert (INSERT)
gr_audit_evidence_select (SELECT)

`gr_audit_findings`

RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`audit_id`	uuid	no
`requirement_id`	uuid	yes
`description`	text	no
`category`	text	no
`severity`	text	no
`assigned_to`	uuid	yes
`due_date`	date	yes
`status`	text	no	’open’::text
`remediation_plan`	text	yes
`resolved_at`	timestamp with time zone	yes
`resolved_by`	uuid	yes
`closed_at`	timestamp with time zone	yes
`closed_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:

assigned_to → pf_profiles(id)
audit_id → gr_audits(id)
closed_by → pf_profiles(id)
created_by → pf_profiles(id)
organization_id → pf_organizations(id)
requirement_id → gr_regulatory_requirements(id)
resolved_by → pf_profiles(id)
updated_by → pf_profiles(id)

Policies:

gr_audit_findings_delete (DELETE)
gr_audit_findings_insert (INSERT)
gr_audit_findings_select (SELECT)
gr_audit_findings_update (UPDATE)

`gr_audit_team_assignments`

RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default
`id`	uuid	no	gen_random_uuid()
`audit_id`	uuid	no
`employee_id`	uuid	no
`role`	text	no
`assigned_at`	timestamp with time zone	no	now()
`assigned_by`	uuid	yes
`custom_fields`	jsonb	no	’{}‘::jsonb
`created_at`	timestamp with time zone	yes	now()
`updated_at`	timestamp with time zone	yes	now()

Foreign keys:

assigned_by → pf_profiles(id)
audit_id → gr_audits(id)
employee_id → hr_employees(id)

Policies:

gr_audit_team_assignments_delete (DELETE)
gr_audit_team_assignments_insert (INSERT)
gr_audit_team_assignments_select (SELECT)
gr_audit_team_assignments_update (UPDATE)

`gr_audits`

RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`site_id`	uuid	yes
`title`	text	no
`description`	text	yes
`audit_type`	text	no
`regulatory_body`	text	yes
`scope`	text	no
`scheduled_date`	date	yes
`actual_start_date`	date	yes
`actual_end_date`	date	yes
`status`	text	no	’planned’::text
`lead_auditor_id`	uuid	yes
`overall_compliance_rate`	numeric(5,2)	yes
`findings_count`	integer	yes	0
`recommendations`	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)
lead_auditor_id → pf_profiles(id)
organization_id → pf_organizations(id)
site_id → pf_sites(id)
updated_by → pf_profiles(id)

Policies:

gr_audits_delete (DELETE)
gr_audits_insert (INSERT)
gr_audits_select (SELECT)
gr_audits_update (UPDATE)

`gr_benchmarking_aggregates`

RLS: ✅ enabled (1 policies)

Column	Type	Nullable	Default
`id`	uuid	no	gen_random_uuid()
`body_type`	text	no
`procedure_category`	text	no
`metric`	text	no
`participating_org_count`	integer	no
`p25`	numeric(7,2)	no
`p50`	numeric(7,2)	no
`p75`	numeric(7,2)	no
`refreshed_at`	timestamp with time zone	no	now()

Policies:

gr_benchmarking_aggregates_select (SELECT)

`gr_benchmarking_consents`

RLS: ✅ enabled (3 policies)

Column	Type	Nullable	Default
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`policy_version`	text	no	’v1’::text
`signed_by`	uuid	no
`signed_name`	text	no
`signed_at`	timestamp with time zone	no	now()
`revoked_at`	timestamp with time zone	yes
`revoked_by`	uuid	yes
`created_at`	timestamp with time zone	no	now()
`updated_at`	timestamp with time zone	no	now()

Foreign keys:

organization_id → pf_organizations(id)

Policies:

gr_benchmarking_consents_insert (INSERT)
gr_benchmarking_consents_select (SELECT)
gr_benchmarking_consents_update (UPDATE)

`gr_board_minutes`

GR-15: Board meeting minutes with approval workflow RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default	Comment
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`meeting_date`	date	no
`meeting_type`	text	no	’regular’::text
`title`	text	no
`attendees`	jsonb	no	’[]‘::jsonb
`content`	text	yes
`document_id`	uuid	yes
`status`	text	no	’draft’::text
`approved_at`	timestamp with time zone	yes
`approved_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

Foreign keys:

approved_by → pf_profiles(id)
created_by → pf_profiles(id)
document_id → pf_documents(id)
organization_id → pf_organizations(id)
updated_by → pf_profiles(id)

Policies:

gr_board_minutes_delete (DELETE)
gr_board_minutes_insert (INSERT)
gr_board_minutes_select (SELECT)
gr_board_minutes_update (UPDATE)

`gr_board_resolutions`

GR-15: Board resolutions with motion, vote, and outcome tracking RLS: ✅ enabled (3 policies)

Column	Type	Nullable	Default	Comment
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`minutes_id`	uuid	yes
`resolution_number`	text	no
`title`	text	no
`description`	text	yes
`motion_by`	uuid	yes
`seconded_by`	uuid	yes
`votes_for`	integer	no	0
`votes_against`	integer	no	0
`votes_abstain`	integer	no	0
`outcome`	text	no	’pending’::text
`effective_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:

created_by → pf_profiles(id)
minutes_id → gr_board_minutes(id)
motion_by → pf_profiles(id)
organization_id → pf_organizations(id)
seconded_by → pf_profiles(id)
updated_by → pf_profiles(id)

Policies:

gr_board_resolutions_insert (INSERT)
gr_board_resolutions_select (SELECT)
gr_board_resolutions_update (UPDATE)

`gr_ceu_credits`

RLS: ✅ enabled (6 policies)

Column	Type	Nullable	Default
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`employee_id`	uuid	no
`completion_id`	uuid	yes
`credits`	numeric(5,2)	no
`category`	text	no
`description`	text	yes
`earned_date`	date	no	CURRENT_DATE
`period_start_date`	date	no
`period_end_date`	date	no
`source_type`	text	no	’internal’::text
`external_provider`	text	yes
`external_course_name`	text	yes
`document_id`	uuid	yes
`verified`	boolean	yes	false
`verified_by`	uuid	yes
`verified_at`	timestamp with time zone	yes
`custom_fields`	jsonb	no	’{}‘::jsonb
`created_at`	timestamp with time zone	no	now()
`created_by`	uuid	yes
`updated_at`	timestamp with time zone	yes	now()

Foreign keys:

completion_id → gr_training_completions(id)
created_by → pf_profiles(id)
document_id → pf_documents(id)
employee_id → hr_employees(id)
organization_id → pf_organizations(id)
verified_by → pf_profiles(id)

Policies:

gr_ceu_credits_delete (DELETE)
gr_ceu_credits_insert_admin (INSERT)
gr_ceu_credits_insert_own (INSERT)
gr_ceu_credits_select_admin (SELECT)
gr_ceu_credits_select_own (SELECT)
gr_ceu_credits_update (UPDATE)

`gr_coi_attestations`

GR-15: Annual conflict-of-interest attestations for board members, executives, and staff RLS: ✅ enabled (3 policies)

Column	Type	Nullable	Default	Comment
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`attestation_year`	integer	no
`profile_id`	uuid	no
`role_at_time`	text	no
`has_conflict`	boolean	yes
`conflict_description`	text	yes
`status`	text	no	’pending’::text
`submitted_at`	timestamp with time zone	yes
`acknowledged_at`	timestamp with time zone	yes
`acknowledged_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

Foreign keys:

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

Policies:

gr_coi_attestations_insert (INSERT)
gr_coi_attestations_select (SELECT)
gr_coi_attestations_update (UPDATE)

`gr_compliance_checks`

RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`requirement_id`	uuid	no
`site_id`	uuid	yes
`check_date`	date	no
`scheduled_date`	date	yes
`check_type`	text	no
`result`	text	no
`result_notes`	text	yes
`score`	numeric(5,2)	yes
`checked_by`	uuid	yes
`verified_by`	uuid	yes
`verified_at`	timestamp with time zone	yes
`approval_status`	text	no	’draft’::text
`form_submission_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:

checked_by → pf_profiles(id)
created_by → pf_profiles(id)
organization_id → pf_organizations(id)
requirement_id → gr_regulatory_requirements(id)
site_id → pf_sites(id)
updated_by → pf_profiles(id)
verified_by → pf_profiles(id)

Policies:

gr_compliance_checks_delete (DELETE)
gr_compliance_checks_insert (INSERT)
gr_compliance_checks_select (SELECT)
gr_compliance_checks_update (UPDATE)

`gr_compliance_evidence`

RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default	Comment
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`requirement_id`	uuid	no
`check_id`	uuid	yes
`evidence_type`	text	no
`evidence_source`	text	yes
`source_id`	uuid	yes
`document_id`	uuid	yes
`title`	text	no
`description`	text	yes
`file_url`	text	yes
`verified_by`	uuid	yes
`verified_at`	timestamp with time zone	yes
`verification_notes`	text	yes
`evidence_quality`	text	yes	’pending’::text
`expires_at`	timestamp with time zone	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()
`source_table`	text	yes		GR-02-EN-01: Originating table name (e.g., gr_training_completions). Mirrors evidence_source.
`collected_at`	timestamp with time zone	yes		GR-02-EN-01: Timestamp when underlying source event occurred (for training: completed_at).

Foreign keys:

check_id → gr_compliance_checks(id)
created_by → pf_profiles(id)
document_id → pf_documents(id)
organization_id → pf_organizations(id)
requirement_id → gr_regulatory_requirements(id)
updated_by → users(id)
verified_by → pf_profiles(id)

Policies:

gr_compliance_evidence_delete (DELETE)
gr_compliance_evidence_insert (INSERT)
gr_compliance_evidence_select (SELECT)
gr_compliance_evidence_update (UPDATE)

`gr_compliance_remediation`

RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`requirement_id`	uuid	no
`check_id`	uuid	yes
`finding_description`	text	no
`severity`	text	no	’medium’::text
`remediation_plan`	text	yes
`responsible_party_id`	uuid	yes
`due_date`	date	no
`status`	text	no	’open’::text
`started_at`	timestamp with time zone	yes
`resolved_at`	timestamp with time zone	yes
`resolved_by`	uuid	yes
`resolution_notes`	text	yes
`resolution_evidence_id`	uuid	yes
`closed_at`	timestamp with time zone	yes
`closed_by`	uuid	yes
`escalated_at`	timestamp with time zone	yes
`escalated_to`	uuid	yes
`escalation_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()
`created_by`	uuid	yes
`updated_by`	uuid	yes

Foreign keys:

check_id → gr_compliance_checks(id)
closed_by → pf_profiles(id)
created_by → pf_profiles(id)
escalated_to → pf_profiles(id)
organization_id → pf_organizations(id)
requirement_id → gr_regulatory_requirements(id)
resolution_evidence_id → gr_compliance_evidence(id)
resolved_by → pf_profiles(id)
responsible_party_id → pf_profiles(id)
updated_by → pf_profiles(id)

Policies:

gr_compliance_remediation_delete (DELETE)
gr_compliance_remediation_insert (INSERT)
gr_compliance_remediation_select (SELECT)
gr_compliance_remediation_update (UPDATE)

`gr_compliance_submissions`

RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`regulatory_body_id`	uuid	yes
`requirement_id`	uuid	yes
`submission_date`	date	no
`submitted_to`	text	no
`submission_type`	text	no
`submission_method`	text	yes
`submission_status`	text	no	’submitted’::text
`submission_document_id`	uuid	yes
`response_document_id`	uuid	yes
`confirmation_number`	text	yes
`response_received_at`	timestamp with time zone	yes
`response_notes`	text	yes
`next_submission_due`	date	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)
regulatory_body_id → gr_regulatory_bodies(id)
requirement_id → gr_regulatory_requirements(id)
response_document_id → pf_documents(id)
submission_document_id → pf_documents(id)
updated_by → pf_profiles(id)

Policies:

gr_compliance_submissions_delete (DELETE)
gr_compliance_submissions_insert (INSERT)
gr_compliance_submissions_select (SELECT)
gr_compliance_submissions_update (UPDATE)

`gr_contract_alerts`

GR-10: Contract alert history and notification tracking RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`contract_id`	uuid	no
`alert_type`	gr_alert_type	no
`alert_date`	date	no
`days_until_trigger`	integer	yes
`message`	text	no
`is_sent`	boolean	yes	false
`sent_at`	timestamp with time zone	yes
`is_acknowledged`	boolean	yes	false
`acknowledged_at`	timestamp with time zone	yes
`acknowledged_by`	uuid	yes
`notification_id`	uuid	yes
`related_record_type`	character varying(50)	yes
`related_record_id`	uuid	yes
`created_at`	timestamp with time zone	no	now()
`updated_at`	timestamp with time zone	no	now()
`updated_by`	uuid	yes

Foreign keys:

acknowledged_by → pf_profiles(id)
contract_id → gr_contracts(id)
notification_id → pf_notifications(id)
organization_id → pf_organizations(id)
updated_by → pf_profiles(id)

Policies:

gr_contract_alerts_delete (DELETE)
gr_contract_alerts_insert (INSERT)
gr_contract_alerts_select (SELECT)
gr_contract_alerts_update (UPDATE)

`gr_contract_amendments`

GR-10: Contract amendments and change history RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`contract_id`	uuid	no
`amendment_number`	integer	no
`title`	character varying(255)	no
`description`	text	yes
`effective_date`	date	no
`changes_summary`	text	yes
`previous_value`	jsonb	yes
`new_value`	jsonb	yes
`document_id`	uuid	yes
`status`	gr_amendment_status	yes	’draft’::gr_amendment_status
`approved_at`	timestamp with time zone	yes
`approved_by`	uuid	yes
`rejected_at`	timestamp with time zone	yes
`rejected_by`	uuid	yes
`rejection_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:

approved_by → pf_profiles(id)
contract_id → gr_contracts(id)
created_by → pf_profiles(id)
organization_id → pf_organizations(id)
rejected_by → pf_profiles(id)
updated_by → pf_profiles(id)

Policies:

gr_contract_amendments_delete (DELETE)
gr_contract_amendments_insert (INSERT)
gr_contract_amendments_select (SELECT)
gr_contract_amendments_update (UPDATE)

`gr_contract_milestones`

GR-10: Contract milestones and key dates RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`contract_id`	uuid	no
`name`	character varying(255)	no
`description`	text	yes
`target_date`	date	no
`status`	gr_milestone_status	yes	’pending’::gr_milestone_status
`completed_at`	timestamp with time zone	yes
`completed_by`	uuid	yes
`completion_notes`	text	yes
`display_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()
`created_by`	uuid	yes
`updated_by`	uuid	yes

Foreign keys:

completed_by → pf_profiles(id)
contract_id → gr_contracts(id)
created_by → pf_profiles(id)
organization_id → pf_organizations(id)
updated_by → pf_profiles(id)

Policies:

gr_contract_milestones_delete (DELETE)
gr_contract_milestones_insert (INSERT)
gr_contract_milestones_select (SELECT)
gr_contract_milestones_update (UPDATE)

`gr_contract_obligations`

GR-10: Contract obligations and deliverables tracking RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`contract_id`	uuid	no
`obligation_type`	gr_obligation_type	no	’other’::gr_obligation_type
`title`	character varying(255)	no
`description`	text	yes
`due_date`	date	no
`reminder_days`	integer	yes	7
`is_recurring`	boolean	yes	false
`recurrence_pattern`	character varying(50)	yes
`next_occurrence`	date	yes
`responsible_party`	character varying(50)	yes	’internal’::character varying
`assigned_to`	uuid	yes
`status`	gr_obligation_status	yes	’pending’::gr_obligation_status
`completed_at`	timestamp with time zone	yes
`completed_by`	uuid	yes
`completion_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:

assigned_to → pf_profiles(id)
completed_by → pf_profiles(id)
contract_id → gr_contracts(id)
created_by → pf_profiles(id)
organization_id → pf_organizations(id)
updated_by → pf_profiles(id)

Policies:

gr_contract_obligations_delete (DELETE)
gr_contract_obligations_insert (INSERT)
gr_contract_obligations_select (SELECT)
gr_contract_obligations_update (UPDATE)

`gr_contract_types`

GR-10: Contract type definitions with org-specific configuration RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`contract_type_code`	gr_contract_type	no
`name`	character varying(100)	no
`description`	text	yes
`default_alert_days`	integer[]	yes	ARRAY[90, 60, 30]
`default_terms_days`	integer	yes	365
`requires_approval`	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

Foreign keys:

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

Policies:

gr_contract_types_delete (DELETE)
gr_contract_types_insert (INSERT)
gr_contract_types_select (SELECT)
gr_contract_types_update (UPDATE)

`gr_contracts`

GR-10: Main contract records with full lifecycle tracking RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default	Comment
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`site_id`	uuid	yes
`contract_number`	character varying(50)	no
`contract_name`	character varying(255)	no
`contract_type_id`	uuid	yes
`contract_type`	gr_contract_type	no	’other’::gr_contract_type
`description`	text	yes
`internal_party_type`	character varying(50)	yes	’organization’::character varying
`internal_party_id`	uuid	yes
`internal_party_name`	character varying(255)	yes
`external_party_type`	character varying(50)	yes	’vendor’::character varying
`external_party_id`	uuid	yes
`external_party_name`	character varying(255)	no
`effective_date`	date	no
`expiration_date`	date	yes
`renewal_type`	gr_renewal_type	yes	’manual’::gr_renewal_type
`auto_renewal_terms`	text	yes
`renewal_notice_days`	integer	yes	60
`termination_notice_days`	integer	yes	30
`contract_value`	numeric(15,2)	yes
`currency`	character varying(3)	yes	’USD’::character varying
`payment_terms`	text	yes
`billing_frequency`	character varying(50)	yes
`status`	gr_contract_status	yes	’draft’::gr_contract_status
`approved_at`	timestamp with time zone	yes
`approved_by`	uuid	yes
`primary_document_id`	uuid	yes
`document_folder_id`	uuid	yes
`has_obligations`	boolean	yes	false
`has_milestones`	boolean	yes	false
`has_sla`	boolean	yes	false
`is_confidential`	boolean	yes	false
`expiration_alert_days`	integer[]	yes	ARRAY[90, 60, 30]
`tags`	text[]	yes	’{}‘::text[]
`search_vector`	tsvector	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. NULL = active record. Use soft delete functions to delete.

Foreign keys:

approved_by → pf_profiles(id)
contract_type_id → gr_contract_types(id)
created_by → pf_profiles(id)
organization_id → pf_organizations(id)
site_id → pf_sites(id)
updated_by → pf_profiles(id)

Policies:

gr_contracts_delete (DELETE)
gr_contracts_insert (INSERT)
gr_contracts_select (SELECT)
gr_contracts_update (UPDATE)

`gr_holiday_calendar`

GR-14: Holiday calendar for business-day deadline calculation. NULL organization_id = system-wide (ARS 1-301). RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	yes
`holiday_date`	date	no
`holiday_name`	text	no
`state_code`	text	no	’AZ’::text

Foreign keys:

organization_id → pf_organizations(id)

Policies:

gr_hc_delete (DELETE)
gr_hc_insert (INSERT)
gr_hc_select (SELECT)
gr_hc_update (UPDATE)

`gr_incident_corrective_actions`

GR-09: Corrective action plans linked to incidents, investigations, and findings. RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`incident_id`	uuid	no
`investigation_id`	uuid	yes
`finding_id`	uuid	yes
`action_description`	text	no
`responsible_party_id`	uuid	yes
`due_date`	date	no
`status`	text	no	’planned’::text
`completed_at`	timestamp with time zone	yes
`completed_by`	uuid	yes
`completion_evidence_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:

completed_by → pf_profiles(id)
completion_evidence_id → gr_compliance_evidence(id)
created_by → pf_profiles(id)
finding_id → gr_incident_findings(id)
incident_id → gr_incidents(id)
investigation_id → gr_incident_investigations(id)
organization_id → pf_organizations(id)
responsible_party_id → pf_profiles(id)
updated_by → pf_profiles(id)

Policies:

incident_coordinators_manage_corrective_actions (INSERT)
incident_coordinators_update_corrective_actions (UPDATE)
incident_corrective_actions_update (UPDATE)
org_members_view_corrective_actions (SELECT)

`gr_incident_findings`

GR-09: Investigation finding records linked to investigations. RLS: ✅ enabled (2 policies)

Column	Type	Nullable	Default
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`investigation_id`	uuid	no
`finding_description`	text	no
`finding_category`	text	no
`finding_severity`	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
`deleted_at`	timestamp with time zone	yes

Foreign keys:

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

Policies:

incident_coordinators_manage_findings (INSERT)
org_members_view_findings (SELECT)

`gr_incident_investigations`

GR-09: Incident investigation records with root cause analysis. RLS: ✅ enabled (3 policies)

Column	Type	Nullable	Default
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`incident_id`	uuid	no
`investigator_id`	uuid	yes
`investigation_start_date`	date	no
`investigation_end_date`	date	yes
`investigation_timeline_days`	integer	yes
`investigation_notes`	text	yes
`investigation_evidence_id`	uuid	yes
`root_cause`	text	yes
`contributing_factors`	text[]	yes
`status`	text	no	’planned’::text
`completed_at`	timestamp with time zone	yes
`completed_by`	uuid	yes
`findings_summary`	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:

completed_by → pf_profiles(id)
created_by → pf_profiles(id)
incident_id → gr_incidents(id)
investigation_evidence_id → pf_documents(id)
investigator_id → pf_profiles(id)
organization_id → pf_organizations(id)
updated_by → pf_profiles(id)

Policies:

incident_coordinators_manage_investigations (INSERT)
incident_coordinators_update_investigations (UPDATE)
org_members_view_investigations (SELECT)

`gr_incident_regulatory_reports`

GR-14: Tracks regulatory reporting obligations per incident per regulatory body. RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default	Comment
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`incident_id`	uuid	no
`regulatory_body`	text	no
`regulation_citation`	text	no
`report_type`	text	no
`statutory_deadline`	timestamp with time zone	no
`deadline_basis`	text	no
`status`	text	no	’draft’::text
`submitted_at`	timestamp with time zone	yes
`submitted_by`	uuid	yes
`agency_reference_number`	text	yes
`acknowledgment_received_at`	timestamp with time zone	yes
`acknowledgment_document_id`	uuid	yes
`report_document_id`	uuid	yes
`narrative`	text	yes
`custom_fields`	jsonb	no	’{}‘::jsonb	Organization-specific metadata (e.g., {“internal_tracking_id”: “RT-001”})
`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:

acknowledgment_document_id → pf_documents(id)
created_by → pf_profiles(id)
incident_id → gr_incidents(id)
organization_id → pf_organizations(id)
report_document_id → pf_documents(id)
submitted_by → pf_profiles(id)
updated_by → pf_profiles(id)

Policies:

gr_irr_delete (DELETE)
gr_irr_insert (INSERT)
gr_irr_select (SELECT)
gr_irr_update (UPDATE)

`gr_incident_risk_links`

GR-09: Links between incidents and GR-05 risk records. RLS: ✅ enabled (3 policies)

Column	Type	Nullable	Default
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`incident_id`	uuid	no
`risk_id`	uuid	no
`link_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

Foreign keys:

created_by → pf_profiles(id)
incident_id → gr_incidents(id)
organization_id → pf_organizations(id)
risk_id → gr_risks(id)
updated_by → pf_profiles(id)

Policies:

incident_coordinators_delete_incident_risk_links (DELETE)
incident_coordinators_manage_incident_risk_links (INSERT)
org_members_view_incident_risk_links (SELECT)

`gr_incidents`

GR-09: Incident records including safety, compliance, operational, and clinical incidents. RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default	Comment
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`site_id`	uuid	yes
`title`	text	no
`description`	text	no
`category`	text	no
`severity`	text	no
`incident_date`	timestamp with time zone	no
`reported_date`	timestamp with time zone	no	now()
`reported_by`	uuid	yes
`location`	text	yes
`assigned_to`	uuid	yes
`status`	text	no	’reported’::text
`resolved_at`	timestamp with time zone	yes
`resolved_by`	uuid	yes
`resolution_summary`	text	yes
`closed_at`	timestamp with time zone	yes
`closed_by`	uuid	yes
`custom_fields`	jsonb	no	’{}‘::jsonb	Organization-specific metadata. Sources: cl_incident_reported, cl_safety_plan_activated, cl_restraint_event_documented, manual entry.
`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)
closed_by → pf_profiles(id)
created_by → pf_profiles(id)
organization_id → pf_organizations(id)
reported_by → pf_profiles(id)
resolved_by → pf_profiles(id)
site_id → pf_sites(id)
updated_by → pf_profiles(id)

Policies:

compliance_officers_delete_incidents (DELETE)
incident_coordinators_manage_incidents (UPDATE)
org_members_report_incidents (INSERT)
org_members_view_incidents (SELECT)

`gr_inservice_reminder_log`

GR-19: Append-only audit ledger of in-service compliance reminders & escalations. DELETEs are blocked at the RLS layer. RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`site_id`	uuid	yes
`course_id`	uuid	no
`employee_id`	uuid	no
`due_date`	date	yes
`reminder_type`	text	no
`channel`	text	no
`recipient`	text	no
`status`	text	no	’queued’::text
`error_message`	text	yes
`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()
`created_by`	uuid	yes
`updated_by`	uuid	yes

Foreign keys:

course_id → gr_training_courses(id)
organization_id → pf_organizations(id)
site_id → pf_sites(id)

Policies:

gr_inservice_reminder_log_insert (INSERT)
gr_inservice_reminder_log_no_delete (DELETE)
gr_inservice_reminder_log_select (SELECT)
gr_inservice_reminder_log_update (UPDATE)

`gr_module_settings`

RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default	Comment
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`default_policy_review_period_days`	integer	yes	365
`require_policy_approval`	boolean	yes	true
`policy_categories`	text[]	yes	ARRAY[‘operational’::text, ‘safety’::text, ‘compliance’::text, ‘hr’::text]
`policy_version_naming`	text	yes	’major.minor’::text
`compliance_alert_days`	integer[]	yes	ARRAY[90, 60, 30, 7]
`auto_create_compliance_tasks`	boolean	yes	true
`default_audit_frequency_days`	integer	yes	365
`default_training_expiration_days`	integer	yes	365
`require_training_acknowledgment`	boolean	yes	true
`training_reminder_days`	integer[]	yes	ARRAY[30, 14, 7]
`allow_self_enrollment`	boolean	yes	true
`document_categories`	text[]	yes	ARRAY[‘policy’::text, ‘procedure’::text, ‘form_template’::text, ‘training_material’::text]
`require_document_approval`	boolean	yes	true
`document_retention_days`	integer	yes	2555
`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	yes	’{}‘::jsonb
`ai_compliance_enabled`	boolean	no	false	Enable AI-assisted compliance advisor features
`qi_enabled`	boolean	yes	false	Enable Quality Improvement feature
`qi_default_cycle_duration_days`	integer	yes	30	Default duration for PDSA cycles in days
`qi_improvement_reminder_days`	integer[]	yes	ARRAY[14, 7, 3]	Days before due date to send improvement action reminders
`qi_pdsa_completion_notifications`	boolean	yes	true	Enable notifications when PDSA cycles are completed
`qi_metric_goal_notifications`	boolean	yes	true	Enable alerts when metrics reach their goal values
`default_expiration_alert_days`	integer[]	yes	ARRAY[90, 60, 30]
`default_renewal_notice_days`	integer	yes	60
`auto_generate_contract_numbers`	boolean	yes	true
`contract_number_prefix`	character varying(20)	yes	’CON’::character varying
`require_contract_approval`	boolean	yes	true
`contract_approval_threshold_amount`	numeric(15,2)	yes
`obligation_reminder_days`	integer	yes	7
`auto_mark_obligations_overdue`	boolean	yes	true
`ai_risk_analysis_enabled`	boolean	yes	false
`ai_renewal_recommendations_enabled`	boolean	yes	false
`policy_number_prefix`	text	yes	’POL’::text	Prefix for policy numbers (e.g., POL)
`policy_number_sequence`	integer	yes	1	Next sequence number for policy generation
`audit_number_prefix`	text	yes	’AUD’::text	Prefix for audit numbers (e.g., AUD)
`audit_number_sequence`	integer	yes	1	Next sequence number for audit generation
`contract_number_sequence`	integer	yes	1
`risk_number_prefix`	text	yes	’RISK’::text	Prefix for risk assessment numbers (e.g., RISK)
`risk_number_sequence`	integer	yes	1	Next sequence number for risk generation
`procedure_number_prefix`	text	yes	’SOP’::text	Prefix for auto-generated procedure numbers (e.g., SOP)
`procedure_number_sequence`	integer	yes	1	Current sequence number for procedure number generation
`procedure_review_reminder_days`	integer	yes	30	Days before review due to send reminders
`procedure_auto_save_interval_seconds`	integer	yes	30	Auto-save interval for procedure editor
`email_sender_address`	text	yes		Optional per-module email sender address (e.g. compliance@domain.com). Falls back to org default if null.
`use_configurable_wizards`	boolean	yes	false	PF-41: Use configurable wizard templates when true; otherwise use hardcoded wizards.
`procedure_overdue_threshold_days`	integer	yes	7	Days after which an in-progress procedure execution is flagged as overdue (default: 7, valid: 1-90)
`procedure_analytics_last_refreshed_at`	timestamp with time zone	yes		Timestamp of last successful gr_procedure_execution_summary materialized view refresh
`tracer_evidence_stale_days`	integer	yes	365	GR-16: Days after which evidence is considered stale for gap analysis (default 365)
`cap_alert_lead_days`	integer	yes	14	GR-16: Days before CAP target_closure_date to send deadline alert notification (default 14)
`gr_incident_reporting_enabled`	boolean	no	false	Feature flag: enables GR-09 incident reporting UI for this organization (default: false)
`gr_incident_investigation_timeline_days`	integer	no	30	Target investigation completion in calendar days (default: 30, per AHCCCS best practice)
`gr_incident_ca_notification_days`	integer[]	no	’{7,3}‘::integer[]	Days before corrective action due date to send reminder notifications (default: 7 days, 3 days)
`regulatory_rule_limit`	integer	no	50	Maximum active regulatory reporting rules per organization (system + org-specific combined). Default: 50.
`deadline_notification_days_before`	integer	no	2	Days before statutory_deadline to send advance notification. Default: 2 (D-2 alert).
`coi_reminder_days`	integer[]	yes	’{30,14,7}‘::integer[]	GR-15: Days before COI attestation deadline to send reminders
`coi_attestation_enabled`	boolean	yes	false	GR-15: Feature flag for COI attestation module
`whistleblower_enabled`	boolean	yes	false	GR-15: Feature flag for whistleblower intake
`retention_warning_days`	integer	yes	90	GR-15: Days before retention deadline to send warning notification
`procedure_step_analytics_last_refreshed_at`	timestamp with time zone	yes
`benchmarking_opt_in`	boolean	no	false
`procedure_benchmarking_last_refreshed_at`	timestamp with time zone	yes
`inservice`	jsonb	no	jsonb_build_object(‘retention_days’, 2555, ‘reminder_offsets_days’, jsonb_build_array(30, 14, 7, 1), ‘escalation_offsets_days’, jsonb_build_array(3, 7), ‘alert_email_recipients’, jsonb_build_array(), ‘rolling_window_months’, 12, ‘allow_grace_period_days’, 7)	GR-19: Org-level configuration for the in-service compliance monitor (retention, reminders, escalations, recipients).

Foreign keys:

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

Policies:

gr_module_settings_delete (DELETE)
gr_module_settings_insert (INSERT)
gr_module_settings_select (SELECT)
gr_module_settings_update (UPDATE)

`gr_policies`

RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default	Comment
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`site_id`	uuid	yes
`title`	text	no
`description`	text	yes
`category`	text	no
`status`	text	no	’draft’::text
`document_id`	uuid	yes
`current_version_number`	integer	no	1
`effective_date`	date	yes
`review_cycle_months`	integer	yes
`last_review_date`	date	yes
`next_review_due_date`	date	yes
`owner_id`	uuid	yes
`approved_by`	uuid	yes
`approved_at`	timestamp with time zone	yes
`deprecated_by`	uuid	yes
`deprecated_at`	timestamp with time zone	yes
`replacement_policy_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
`deleted_at`	timestamp with time zone	yes		Soft delete timestamp. NULL = active record. Use soft delete functions to delete.
`policy_domain`	text	yes	’governance’::text	Domain filter: governance, hr, safety, clinical, it, financial, privacy
`document_storage_path`	text	yes		Supabase Storage path for the policy document file
`acknowledgment_frequency`	text	yes		How often re-acknowledgment is required: one_time, annual, on_update

Foreign keys:

approved_by → pf_profiles(id)
created_by → pf_profiles(id)
deprecated_by → pf_profiles(id)
document_id → pf_documents(id)
organization_id → pf_organizations(id)
owner_id → pf_profiles(id)
replacement_policy_id → gr_policies(id)
site_id → pf_sites(id)
updated_by → pf_profiles(id)

Policies:

gr_policies_delete (DELETE)
gr_policies_insert (INSERT)
gr_policies_select (SELECT)
gr_policies_update (UPDATE)

`gr_policy_acknowledgments`

RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default	Comment
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`policy_id`	uuid	no
`policy_version_id`	uuid	yes
`employee_id`	uuid	no
`assigned_at`	timestamp with time zone	no	now()
`assigned_by`	uuid	yes
`due_date`	date	no
`is_required`	boolean	no	true
`acknowledged_at`	timestamp with time zone	yes
`acknowledged_by`	uuid	yes
`acknowledgment_form_id`	uuid	yes		GR-01-EN-02: Reference to the PF-16 form definition set (entity_type=‘gr_policy_acknowledgment’) used to render this acknowledgment. NULL = built-in default form (read_confirmation + notes).
`status`	text	no	’pending’::text
`notes`	text	yes
`custom_fields`	jsonb	no	’{}‘::jsonb	GR-01-EN-02: Submitted acknowledgment form response. Flat JSON object keyed by PF-16 field_key (e.g., {“read_confirmation”: true, “notes”: ”…”}). Default form shape is back-compat with pre-EN-02 rows.
`created_at`	timestamp with time zone	no	now()
`updated_at`	timestamp with time zone	no	now()
`ip_address`	text	yes		IP address captured at acknowledgment time for legal defensibility
`user_agent`	text	yes		Browser user agent captured at acknowledgment time for legal defensibility

Foreign keys:

acknowledged_by → pf_profiles(id)
acknowledgment_form_id → fw_form_submissions(id)
assigned_by → pf_profiles(id)
employee_id → hr_employees(id)
organization_id → pf_organizations(id)
policy_id → gr_policies(id)
policy_version_id → gr_policy_versions(id)

Policies:

gr_policy_acks_delete (DELETE)
gr_policy_acks_insert (INSERT)
gr_policy_acks_select (SELECT)
gr_policy_acks_update (UPDATE)

`gr_policy_assignments`

RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default	Comment
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`policy_id`	uuid	no
`assignment_type`	text	no
`role_name`	text	yes
`department_id`	uuid	yes
`site_id`	uuid	yes
`employee_id`	uuid	yes
`due_days`	integer	no	30
`is_required`	boolean	no	true
`effective_date`	date	no	CURRENT_DATE
`end_date`	date	yes
`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:

created_by → pf_profiles(id)
department_id → pf_departments(id)
employee_id → hr_employees(id)
organization_id → pf_organizations(id)
policy_id → gr_policies(id)
site_id → pf_sites(id)
updated_by → users(id)

Policies:

gr_policy_assignments_delete (DELETE)
gr_policy_assignments_insert (INSERT)
gr_policy_assignments_select (SELECT)
gr_policy_assignments_update (UPDATE)

`gr_policy_regulatory_links`

RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default	Comment
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`policy_id`	uuid	no
`regulatory_body`	text	no
`reference_code`	text	yes
`reference_description`	text	yes
`is_primary_evidence`	boolean	yes	false
`evidence_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:

created_by → pf_profiles(id)
organization_id → pf_organizations(id)
policy_id → gr_policies(id)
updated_by → users(id)

Policies:

gr_policy_reg_links_delete (DELETE)
gr_policy_reg_links_insert (INSERT)
gr_policy_reg_links_select (SELECT)
gr_policy_reg_links_update (UPDATE)

`gr_policy_versions`

RLS: ✅ enabled (3 policies)

Column	Type	Nullable	Default	Comment
`id`	uuid	no	gen_random_uuid()
`policy_id`	uuid	no
`organization_id`	uuid	no
`version_number`	integer	no
`change_summary`	text	yes
`document_id`	uuid	yes
`status`	text	no
`effective_date`	date	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:

created_by → pf_profiles(id)
document_id → pf_documents(id)
organization_id → pf_organizations(id)
policy_id → gr_policies(id)
updated_by → users(id)

Policies:

gr_policy_versions_delete (DELETE)
gr_policy_versions_insert (INSERT)
gr_policy_versions_select (SELECT)

`gr_procedure_compliance_score_snapshots`

RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`category`	text	no
`snapshot_date`	date	no
`procedure_count`	integer	no	0
`avg_completion_rate`	numeric(5,2)	no	0
`coverage_rate`	numeric(5,2)	no	0
`effectiveness_score`	numeric(5,2)	no	0
`created_at`	timestamp with time zone	no	now()
`updated_at`	timestamp with time zone	no	now()

Foreign keys:

organization_id → pf_organizations(id)

Policies:

gr_compliance_snapshots_delete (DELETE)
gr_compliance_snapshots_insert (INSERT)
gr_compliance_snapshots_select (SELECT)
gr_compliance_snapshots_update (UPDATE)

`gr_procedure_executions`

Tracks individual executions of procedures with step-by-step progress RLS: ✅ enabled (3 policies)

Column	Type	Nullable	Default	Comment
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`procedure_id`	uuid	no
`procedure_version_id`	uuid	yes
`executed_by`	uuid	no
`started_at`	timestamp with time zone	no	now()
`completed_at`	timestamp with time zone	yes
`status`	text	no	’in_progress’::text
`step_progress`	jsonb	no	’{}‘::jsonb	JSON object tracking completion status of each step { step_id: { completed: boolean, completed_at: timestamp, signature?: string } }
`current_step_id`	text	yes
`context_type`	text	yes
`context_id`	uuid	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

Foreign keys:

created_by → pf_profiles(id)
executed_by → pf_profiles(id)
organization_id → pf_organizations(id)
procedure_id → gr_procedures(id)
procedure_version_id → gr_procedure_versions(id)
updated_by → pf_profiles(id)

Policies:

gr_procedure_executions_insert (INSERT)
gr_procedure_executions_select (SELECT)
gr_procedure_executions_update (UPDATE)

`gr_procedure_policy_links`

Links procedures to policies they implement or support RLS: ✅ enabled (3 policies)

Column	Type	Nullable	Default
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`procedure_id`	uuid	no
`policy_id`	uuid	no
`relationship_type`	text	yes	’implements’::text
`notes`	text	yes
`created_at`	timestamp with time zone	no	now()
`created_by`	uuid	yes

Foreign keys:

created_by → pf_profiles(id)
organization_id → pf_organizations(id)
policy_id → gr_policies(id)
procedure_id → gr_procedures(id)

Policies:

gr_procedure_policy_links_delete (DELETE)
gr_procedure_policy_links_insert (INSERT)
gr_procedure_policy_links_select (SELECT)

`gr_procedure_steps`

Denormalized procedure steps for efficient querying and execution tracking RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`procedure_id`	uuid	no
`node_id`	text	no
`step_order`	integer	no	0
`step_type`	text	no	’action’::text
`title`	text	no
`instructions`	text	yes
`responsible_role`	text	yes
`estimated_duration_minutes`	integer	yes
`requires_signature`	boolean	yes	false
`verification_criteria`	text	yes
`decision_options`	jsonb	yes	’[]‘::jsonb
`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

Foreign keys:

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

Policies:

gr_procedure_steps_delete (DELETE)
gr_procedure_steps_insert (INSERT)
gr_procedure_steps_select (SELECT)
gr_procedure_steps_update (UPDATE)

`gr_procedure_template_regulatory_bodies`

GR-12: M:N link from procedure templates to gr_regulatory_bodies (replaces legacy TEXT[]). RLS: ✅ enabled (3 policies)

Column	Type	Nullable	Default	Comment
`template_id`	uuid	no
`regulatory_body_id`	uuid	no

Foreign keys:

regulatory_body_id → gr_regulatory_bodies(id)
template_id → gr_procedure_templates(id)

Policies:

gr_procedure_template_regulatory_bodies_delete (DELETE)
gr_procedure_template_regulatory_bodies_insert (INSERT)
gr_procedure_template_regulatory_bodies_select (SELECT)

`gr_procedure_templates`

GR-12: Global and org-level procedure templates. NULL organization_id = global/system template visible to all orgs. RLS: ✅ enabled (5 policies)

Column	Type	Nullable	Default	Comment
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	yes		NULL = global/system template; non-NULL = org-private.
`title`	text	no
`description`	text	yes
`category`	text	no
`tags`	text[]	no	’{}‘::text[]
`workflow_nodes`	jsonb	no	’[]‘::jsonb
`workflow_edges`	jsonb	no	’[]‘::jsonb
`suggested_policy_category`	text	yes
`is_active`	boolean	no	true
`version`	integer	no	1
`source_procedure_id`	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		Soft delete timestamp. NULL = active.

Foreign keys:

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

Policies:

gr_procedure_templates_admin_select (SELECT)
gr_procedure_templates_delete (DELETE)
gr_procedure_templates_insert (INSERT)
gr_procedure_templates_select (SELECT)
gr_procedure_templates_update (UPDATE)

`gr_procedure_versions`

Immutable version history for procedures RLS: ✅ enabled (2 policies)

Column	Type	Nullable	Default
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`procedure_id`	uuid	no
`version_number`	integer	no
`change_summary`	text	yes
`title`	text	no
`description`	text	yes
`workflow_nodes`	jsonb	no	’[]‘::jsonb
`workflow_edges`	jsonb	no	’[]‘::jsonb
`approved_by`	uuid	yes
`approved_at`	timestamp with time zone	yes
`created_at`	timestamp with time zone	no	now()
`created_by`	uuid	yes

Foreign keys:

approved_by → pf_profiles(id)
created_by → pf_profiles(id)
organization_id → pf_organizations(id)
procedure_id → gr_procedures(id)

Policies:

gr_procedure_versions_insert (INSERT)
gr_procedure_versions_select (SELECT)

`gr_procedures`

Standard operating procedures with visual workflow definitions RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default	Comment
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`site_id`	uuid	yes
`procedure_number`	text	no
`title`	text	no
`description`	text	yes
`category`	text	no	’operational’::text
`status`	text	no	’draft’::text
`workflow_nodes`	jsonb	yes	’[]‘::jsonb	React Flow nodes array defining procedure steps
`workflow_edges`	jsonb	yes	’[]‘::jsonb	React Flow edges array defining step connections
`current_version_number`	integer	no	1
`implements_policy_id`	uuid	yes
`owner_id`	uuid	yes
`approved_by`	uuid	yes
`approved_at`	timestamp with time zone	yes
`effective_date`	date	yes
`review_date`	date	yes
`review_cycle_months`	integer	yes	12
`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

Foreign keys:

approved_by → pf_profiles(id)
created_by → pf_profiles(id)
implements_policy_id → gr_policies(id)
organization_id → pf_organizations(id)
owner_id → pf_profiles(id)
site_id → pf_sites(id)
updated_by → pf_profiles(id)

Policies:

gr_procedures_delete (DELETE)
gr_procedures_insert (INSERT)
gr_procedures_select (SELECT)
gr_procedures_update (UPDATE)

`gr_qi_improvements`

RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default	Comment
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`project_id`	uuid	yes
`pdsa_cycle_id`	uuid	yes
`metric_id`	uuid	yes
`description`	text	no
`responsible_party_id`	uuid	yes
`due_date`	date	yes
`status`	text	no	’planned’::text
`completed_at`	timestamp with time zone	yes
`completed_by`	uuid	yes
`completion_evidence_id`	uuid	yes
`notes`	text	yes
`custom_fields`	jsonb	no	’{}‘::jsonb	Organization-specific metadata (e.g., {“priority”: “high”, “cost_estimate”: 5000})
`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:

completed_by → pf_profiles(id)
completion_evidence_id → gr_compliance_evidence(id)
created_by → pf_profiles(id)
metric_id → gr_qi_metrics(id)
organization_id → pf_organizations(id)
pdsa_cycle_id → gr_qi_pdsa_cycles(id)
project_id → gr_qi_projects(id)
responsible_party_id → pf_profiles(id)
updated_by → pf_profiles(id)

Policies:

gr_qi_improvements_delete_policy (DELETE)
gr_qi_improvements_insert_policy (INSERT)
gr_qi_improvements_select_policy (SELECT)
gr_qi_improvements_update_policy (UPDATE)

`gr_qi_metric_measurements`

RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default	Comment
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`metric_id`	uuid	no
`measured_value`	numeric	no
`measurement_date`	date	no
`notes`	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)
metric_id → gr_qi_metrics(id)
organization_id → pf_organizations(id)
updated_by → users(id)

Policies:

gr_qi_metric_measurements_delete (DELETE)
gr_qi_metric_measurements_insert (INSERT)
gr_qi_metric_measurements_select (SELECT)
gr_qi_metric_measurements_update (UPDATE)

`gr_qi_metrics`

RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default	Comment
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`project_id`	uuid	yes
`name`	text	no
`description`	text	yes
`category`	text	no
`measurement_method`	text	yes
`measurement_frequency`	text	no
`baseline_value`	numeric	yes
`goal_value`	numeric	yes
`current_value`	numeric	yes
`trend`	text	yes
`is_active`	boolean	no	true
`custom_fields`	jsonb	no	’{}‘::jsonb	Organization-specific metadata (e.g., {“unit_of_measure”: “percentage”, “data_source”: “ehr”})
`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)
project_id → gr_qi_projects(id)
updated_by → pf_profiles(id)

Policies:

gr_qi_metrics_delete_policy (DELETE)
gr_qi_metrics_insert_policy (INSERT)
gr_qi_metrics_select_policy (SELECT)
gr_qi_metrics_update_policy (UPDATE)

`gr_qi_pdsa_cycles`

RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default	Comment
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`project_id`	uuid	no
`cycle_number`	integer	no
`current_phase`	text	no	’plan’::text
`plan_description`	text	yes
`plan_activities`	text[]	yes
`plan_outcomes`	text	yes
`do_description`	text	yes
`do_activities`	text[]	yes
`do_evidence_id`	uuid	yes
`study_description`	text	yes
`study_data`	jsonb	yes
`study_results`	text	yes
`study_conclusions`	text	yes
`act_description`	text	yes
`act_decisions`	text[]	yes
`act_next_steps`	text	yes
`status`	text	no	’planning’::text
`completed_at`	timestamp with time zone	yes
`completed_by`	uuid	yes
`custom_fields`	jsonb	no	’{}‘::jsonb	Organization-specific metadata (e.g., {“data_collection_method”: “chart_review”, “sample_size”: 50})
`created_at`	timestamp with time zone	no	now()
`updated_at`	timestamp with time zone	no	now()
`created_by`	uuid	yes
`updated_by`	uuid	yes
`plan_start_date`	date	yes		Planned start date for this PDSA cycle
`plan_end_date`	date	yes		Planned end date for this PDSA cycle
`hypothesis`	text	yes		The hypothesis being tested in this PDSA cycle

Foreign keys:

completed_by → pf_profiles(id)
created_by → pf_profiles(id)
do_evidence_id → pf_documents(id)
organization_id → pf_organizations(id)
project_id → gr_qi_projects(id)
updated_by → pf_profiles(id)

Policies:

gr_qi_pdsa_cycles_delete_policy (DELETE)
gr_qi_pdsa_cycles_insert_policy (INSERT)
gr_qi_pdsa_cycles_select_policy (SELECT)
gr_qi_pdsa_cycles_update_policy (UPDATE)

`gr_qi_project_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
`category`	text	no	’operational’::text
`default_objective`	text	yes
`default_scope`	text	yes
`default_duration_days`	integer	yes
`metric_templates`	jsonb	no	’[]‘::jsonb
`improvement_checklist`	jsonb	no	’[]‘::jsonb
`pdsa_cycle_templates`	jsonb	no	’[]‘::jsonb
`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:

gr_qi_project_templates_delete (DELETE)
gr_qi_project_templates_insert (INSERT)
gr_qi_project_templates_select (SELECT)
gr_qi_project_templates_update (UPDATE)

`gr_qi_projects`

RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default	Comment
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`site_id`	uuid	yes
`title`	text	no
`description`	text	yes
`objective`	text	no
`scope`	text	yes
`category`	text	no
`owner_id`	uuid	yes
`start_date`	date	yes
`target_completion_date`	date	yes
`actual_completion_date`	date	yes
`status`	text	no	’planning’::text
`custom_fields`	jsonb	no	’{}‘::jsonb	Organization-specific metadata (e.g., {“project_number”: “QI-2024-001”, “carf_standard”: “1.H.4”})
`created_at`	timestamp with time zone	no	now()
`updated_at`	timestamp with time zone	no	now()
`created_by`	uuid	yes
`updated_by`	uuid	yes
`source_event_id`	uuid	yes		GR-13-EN-01: References the fw_domain_events row that auto-created this draft QI project (e.g. procedure_gap_identified). NULL for manually created projects.

Foreign keys:

created_by → pf_profiles(id)
organization_id → pf_organizations(id)
owner_id → pf_profiles(id)
site_id → pf_sites(id)
source_event_id → fw_domain_events(id)
updated_by → pf_profiles(id)

Policies:

gr_qi_projects_delete_policy (DELETE)
gr_qi_projects_insert_policy (INSERT)
gr_qi_projects_select_policy (SELECT)
gr_qi_projects_update_policy (UPDATE)

`gr_regulatory_bodies`

RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	yes
`name`	text	no
`body_type`	text	no
`contact_name`	text	yes
`contact_email`	text	yes
`contact_phone`	text	yes
`contact_address`	text	yes
`website_url`	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

Foreign keys:

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

Policies:

gr_regulatory_bodies_delete (DELETE)
gr_regulatory_bodies_insert (INSERT)
gr_regulatory_bodies_select (SELECT)
gr_regulatory_bodies_update (UPDATE)

`gr_regulatory_change_events`

GR-17: Detected regulatory changes per source, with severity classification and approval workflow. RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`source_id`	uuid	no
`external_change_id`	text	yes
`change_type`	text	no	’update’::text
`title`	text	no
`summary`	text	yes
`source_url`	text	yes
`detected_at`	timestamp with time zone	no	now()
`effective_date`	date	yes
`published_date`	date	yes
`severity`	text	no	’informational’::text
`severity_overridden`	boolean	no	false
`severity_override_reason`	text	yes
`status`	text	no	’detected’::text
`reviewed_at`	timestamp with time zone	yes
`reviewed_by`	uuid	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 → users(id)
organization_id → pf_organizations(id)
reviewed_by → users(id)
source_id → gr_regulatory_sources(id)
updated_by → users(id)

Policies:

gr_reg_change_events_delete (DELETE)
gr_reg_change_events_insert (INSERT)
gr_reg_change_events_select (SELECT)
gr_reg_change_events_update (UPDATE)

`gr_regulatory_change_impacts`

GR-17: Per-event impact rows linking a regulatory change to affected requirements/policies/KB. RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default	Comment
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`change_event_id`	uuid	no
`impact_type`	text	no
`target_id`	uuid	yes
`target_label`	text	yes
`status`	text	no	’pending’::text
`assigned_to`	uuid	yes
`notes`	text	yes
`pf_kb_refresh_status`	text	yes
`pf_kb_refresh_attempts`	jsonb	no	’[]‘::jsonb	GR-17: Array of KB refresh attempts. Each entry: {attempt_number:int, attempted_at:timestamptz, http_status:int, error_message:text\|null}.
`pf_kb_last_refreshed_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:

assigned_to → users(id)
change_event_id → gr_regulatory_change_events(id)
created_by → users(id)
organization_id → pf_organizations(id)
updated_by → users(id)

Policies:

gr_reg_change_impacts_delete (DELETE)
gr_reg_change_impacts_insert (INSERT)
gr_reg_change_impacts_select (SELECT)
gr_reg_change_impacts_update (UPDATE)

`gr_regulatory_change_snapshots`

GR-17: Append-only content snapshots from regulatory sources for diff history. RLS: ✅ enabled (2 policies)

Column	Type	Nullable	Default
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`source_id`	uuid	no
`change_event_id`	uuid	yes
`snapshot_at`	timestamp with time zone	no	now()
`content_hash`	text	no
`content_excerpt`	text	yes
`http_status`	integer	yes
`byte_size`	integer	yes
`created_at`	timestamp with time zone	no	now()
`created_by`	uuid	yes

Foreign keys:

change_event_id → gr_regulatory_change_events(id)
created_by → users(id)
organization_id → pf_organizations(id)
source_id → gr_regulatory_sources(id)

Policies:

gr_reg_change_snapshots_insert (INSERT)
gr_reg_change_snapshots_select (SELECT)

`gr_regulatory_reporting_rules`

GR-14: Configuration rules defining which incident types trigger regulatory reporting obligations. RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default	Comment
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	yes
`regulatory_body`	text	no
`regulation_citation`	text	no
`report_type`	text	no
`trigger_conditions`	jsonb	no		JSONB conditions matched against incident fields: {category: string[], severity?: string[], involves_restraint?: boolean}
`deadline_hours`	integer	no
`deadline_type`	text	no	’calendar_hours’::text
`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)
organization_id → pf_organizations(id)
updated_by → pf_profiles(id)

Policies:

gr_rrr_delete (DELETE)
gr_rrr_insert (INSERT)
gr_rrr_select (SELECT)
gr_rrr_update (UPDATE)

`gr_regulatory_requirements`

RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`site_id`	uuid	yes
`regulatory_body_id`	uuid	yes
`title`	text	no
`description`	text	yes
`category`	text	no
`reference_code`	text	yes
`frequency`	text	no
`scope`	text	no
`priority`	text	no	’medium’::text
`deadline_date`	date	yes
`next_check_due_date`	date	yes
`status`	text	no	’pending’::text
`last_check_date`	date	yes
`last_verified_by`	uuid	yes
`last_verified_at`	timestamp with time zone	yes
`applies_to_roles`	text[]	yes
`notification_days_before`	integer	yes	30
`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)
last_verified_by → pf_profiles(id)
organization_id → pf_organizations(id)
regulatory_body_id → gr_regulatory_bodies(id)
site_id → pf_sites(id)
updated_by → pf_profiles(id)

Policies:

gr_regulatory_requirements_delete (DELETE)
gr_regulatory_requirements_insert (INSERT)
gr_regulatory_requirements_select (SELECT)
gr_regulatory_requirements_update (UPDATE)

`gr_regulatory_sources`

GR-17: External regulator endpoints (AHCCCS AMPM, DHCS, accreditors) the watcher polls. RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`name`	text	no
`description`	text	yes
`source_url`	text	no
`source_type`	text	no	’http’::text
`regulatory_body`	text	yes
`jurisdiction`	text	yes
`is_active`	boolean	no	true
`poll_interval_minutes`	integer	no	1440
`last_polled_at`	timestamp with time zone	yes
`last_change_detected_at`	timestamp with time zone	yes
`last_error`	text	yes
`last_error_at`	timestamp with time zone	yes
`consecutive_failures`	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 → users(id)
organization_id → pf_organizations(id)
updated_by → users(id)

Policies:

gr_regulatory_sources_delete (DELETE)
gr_regulatory_sources_insert (INSERT)
gr_regulatory_sources_select (SELECT)
gr_regulatory_sources_update (UPDATE)

`gr_risk_assessments`

Risk assessment history tracking likelihood, impact, and scores over time RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`risk_id`	uuid	no
`likelihood`	integer	no
`impact`	integer	no
`risk_score`	integer	no
`risk_rating`	text	no
`assessment_notes`	text	yes
`assessment_type`	text	yes	’standard’::text
`assessed_at`	timestamp with time zone	no	now()
`assessed_by`	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:

assessed_by → pf_profiles(id)
organization_id → pf_organizations(id)
risk_id → gr_risks(id)

Policies:

gr_risk_assessments_delete (DELETE)
gr_risk_assessments_insert (INSERT)
gr_risk_assessments_select (SELECT)
gr_risk_assessments_update (UPDATE)

`gr_risk_links`

Links risks to compliance requirements, audit findings, incidents, and policies RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default	Comment
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`risk_id`	uuid	no
`link_type`	text	no
`requirement_id`	uuid	yes
`finding_id`	uuid	yes
`incident_id`	uuid	yes
`policy_id`	uuid	yes
`link_notes`	text	yes
`created_at`	timestamp with time zone	no	now()
`created_by`	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)
finding_id → gr_audit_findings(id)
organization_id → pf_organizations(id)
policy_id → gr_policies(id)
requirement_id → gr_regulatory_requirements(id)
risk_id → gr_risks(id)
updated_by → users(id)

Policies:

gr_risk_links_delete (DELETE)
gr_risk_links_insert (INSERT)
gr_risk_links_select (SELECT)
gr_risk_links_update (UPDATE)

`gr_risk_mitigations`

Mitigation action plans for addressing identified risks RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`risk_id`	uuid	no
`strategy`	text	no
`mitigation_plan`	text	no
`action_description`	text	yes
`responsible_party_id`	uuid	yes
`due_date`	date	yes
`status`	text	no	’planned’::text
`completed_at`	timestamp with time zone	yes
`completed_by`	uuid	yes
`completion_evidence_id`	uuid	yes
`residual_likelihood`	integer	yes
`residual_impact`	integer	yes
`residual_risk_score`	integer	yes
`residual_risk_rating`	text	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:

completed_by → pf_profiles(id)
completion_evidence_id → gr_compliance_evidence(id)
created_by → pf_profiles(id)
organization_id → pf_organizations(id)
responsible_party_id → pf_profiles(id)
risk_id → gr_risks(id)
updated_by → pf_profiles(id)

Policies:

gr_risk_mitigations_delete (DELETE)
gr_risk_mitigations_insert (INSERT)
gr_risk_mitigations_select (SELECT)
gr_risk_mitigations_update (UPDATE)

`gr_risks`

Core risk register storing all identified organizational risks RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`site_id`	uuid	yes
`title`	text	no
`description`	text	yes
`category`	text	no
`source`	text	yes
`owner_id`	uuid	yes
`current_likelihood`	integer	yes
`current_impact`	integer	yes
`current_risk_score`	integer	yes
`current_risk_rating`	text	yes
`residual_likelihood`	integer	yes
`residual_impact`	integer	yes
`residual_risk_score`	integer	yes
`residual_risk_rating`	text	yes
`last_assessed_at`	timestamp with time zone	yes
`last_assessed_by`	uuid	yes
`status`	text	no	’active’::text
`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)
last_assessed_by → pf_profiles(id)
organization_id → pf_organizations(id)
owner_id → pf_profiles(id)
site_id → pf_sites(id)
updated_by → pf_profiles(id)

Policies:

gr_risks_delete (DELETE)
gr_risks_insert (INSERT)
gr_risks_select (SELECT)
gr_risks_update (UPDATE)

`gr_state_kb_collections`

GR-06-EN-01: Platform-global catalog of state regulatory knowledge base collections (public regulatory text, e.g. AHCCCS AMPM, ARS statutes). Shared across all tenants by design; keyed by (state_code, jurisdiction_program, collection_name). Tenant-specific overrides belong in PF-96 jurisdiction profiles, not here. Writes restricted to platform_admin or pf.jurisdiction.profiles.manage. RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default
`id`	uuid	no	gen_random_uuid()
`state_code`	text	no
`jurisdiction_program`	text	no
`collection_name`	text	no
`description`	text	yes
`source_url`	text	yes
`effective_date`	date	yes
`last_reviewed_at`	timestamp with time zone	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:

created_by → pf_profiles(id)
updated_by → pf_profiles(id)

Policies:

gr_state_kb_collections_delete (DELETE)
gr_state_kb_collections_insert (INSERT)
gr_state_kb_collections_select (SELECT)
gr_state_kb_collections_update (UPDATE)

`gr_state_kb_documents`

GR-06-EN-01: Platform-global regulatory documents (public state Medicaid manuals, AHCCCS policies, statutes) used as RAG context by the AI Compliance Advisor. Shared across all tenants by design; scoped by collection -> (state_code, jurisdiction_program). Writes restricted to platform_admin or pf.jurisdiction.profiles.manage. RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default
`id`	uuid	no	gen_random_uuid()
`collection_id`	uuid	no
`document_title`	text	no
`document_type`	text	no
`citation`	text	yes
`source_url`	text	yes
`content_text`	text	yes
`effective_date`	date	yes
`last_reviewed_at`	timestamp with time zone	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

Foreign keys:

collection_id → gr_state_kb_collections(id)
created_by → pf_profiles(id)
updated_by → pf_profiles(id)

Policies:

gr_state_kb_documents_delete (DELETE)
gr_state_kb_documents_insert (INSERT)
gr_state_kb_documents_select (SELECT)
gr_state_kb_documents_update (UPDATE)

`gr_training_completions`

RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default	Comment
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`enrollment_id`	uuid	no
`course_id`	uuid	no
`employee_id`	uuid	no
`completed_at`	timestamp with time zone	no	now()
`score`	integer	yes
`passed`	boolean	no	true
`attempts`	integer	yes	1
`time_spent_minutes`	integer	yes
`verification_method`	text	yes	’system’::text
`verified_by`	uuid	yes
`verified_at`	timestamp with time zone	yes
`certificate_number`	text	yes
`certificate_document_id`	uuid	yes
`expires_at`	timestamp with time zone	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()
`ceu_credits_awarded`	numeric(6,2)	no	0	GR-02-EN-01: Continuing-education credits awarded by this completion (defaults to 0).
`ceu_credit_type`	text	yes		GR-02-EN-01: Category of CEU credit. NULL when ceu_credits_awarded = 0.

Foreign keys:

certificate_document_id → pf_documents(id)
course_id → gr_training_courses(id)
created_by → pf_profiles(id)
employee_id → hr_employees(id)
enrollment_id → gr_training_enrollments(id)
organization_id → pf_organizations(id)
updated_by → users(id)
verified_by → pf_profiles(id)

Policies:

gr_training_completions_insert (INSERT)
gr_training_completions_select_admin (SELECT)
gr_training_completions_select_own (SELECT)
gr_training_completions_update (UPDATE)

`gr_training_courses`

RLS: ✅ enabled (5 policies)

Column	Type	Nullable	Default	Comment
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`site_id`	uuid	yes
`title`	text	no
`description`	text	yes
`category`	text	no	’general’::text
`course_code`	text	yes
`delivery_method`	text	no	’online’::text
`duration_minutes`	integer	yes
`passing_score`	integer	yes
`max_attempts`	integer	yes	3
`ceu_credits`	numeric(5,2)	yes	0
`ceu_category`	text	yes
`validity_days`	integer	yes
`is_recurring`	boolean	yes	false
`recurrence_interval_months`	integer	yes
`content_url`	text	yes
`document_id`	uuid	yes
`status`	text	no	’draft’::text
`is_required`	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
`is_system_seeded`	boolean	no	false	GR-19 / GR-02-EN-05: TRUE when row was created from a platform-seeded baseline template.
`is_active`	boolean	no	true	GR-19 / GR-02-EN-05: FALSE soft-disables the course without losing completion history.
`seed_template_id`	text	yes		GR-19 / GR-02-EN-05: Stable template id linking org-specific course back to its platform-seeded template.

Foreign keys:

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

Policies:

gr_training_courses_delete (DELETE)
gr_training_courses_insert (INSERT)
gr_training_courses_select_admin (SELECT)
gr_training_courses_select_org (SELECT)
gr_training_courses_update (UPDATE)

`gr_training_enrollments`

RLS: ✅ enabled (6 policies)

Column	Type	Nullable	Default	Comment
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`course_id`	uuid	no
`employee_id`	uuid	no
`status`	text	no	’assigned’::text
`progress_percent`	integer	yes	0
`assigned_date`	timestamp with time zone	no	now()
`due_date`	timestamp with time zone	yes
`started_at`	timestamp with time zone	yes
`assigned_by`	uuid	yes
`assignment_reason`	text	yes
`is_mandatory`	boolean	yes	true
`waived_at`	timestamp with time zone	yes
`waived_by`	uuid	yes
`waiver_reason`	text	yes
`custom_fields`	jsonb	no	’{}‘::jsonb
`created_at`	timestamp with time zone	no	now()
`updated_at`	timestamp with time zone	no	now()
`overdue_event_published_at`	timestamp with time zone	yes		GR-02-EN-01: Set by enrollment_overdue publisher trigger to suppress duplicate event emission.

Foreign keys:

assigned_by → pf_profiles(id)
course_id → gr_training_courses(id)
employee_id → hr_employees(id)
organization_id → pf_organizations(id)
waived_by → pf_profiles(id)

Policies:

gr_training_enrollments_delete (DELETE)
gr_training_enrollments_insert (INSERT)
gr_training_enrollments_select_admin (SELECT)
gr_training_enrollments_select_own (SELECT)
gr_training_enrollments_update_admin (UPDATE)
gr_training_enrollments_update_own (UPDATE)

`gr_training_policy_links`

RLS: ✅ enabled (3 policies)

Column	Type	Nullable	Default	Comment
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`course_id`	uuid	no
`policy_id`	uuid	no
`is_required`	boolean	yes	true
`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:

course_id → gr_training_courses(id)
created_by → pf_profiles(id)
organization_id → pf_organizations(id)
policy_id → gr_policies(id)
updated_by → users(id)

Policies:

gr_training_policy_links_delete (DELETE)
gr_training_policy_links_insert (INSERT)
gr_training_policy_links_select (SELECT)

`gr_training_requirement_links`

GR-02-EN-01: Maps training courses to regulatory requirements they satisfy. Used by training_completed event consumers to auto-create compliance evidence. RLS: ✅ enabled (4 policies)

Column	Type	Nullable	Default
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`course_id`	uuid	no
`regulatory_requirement_id`	uuid	no
`is_required`	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:

course_id → gr_training_courses(id)
organization_id → pf_organizations(id)
regulatory_requirement_id → gr_regulatory_requirements(id)

Policies:

gr_training_requirement_links_delete (DELETE)
gr_training_requirement_links_insert (INSERT)
gr_training_requirement_links_select (SELECT)
gr_training_requirement_links_update (UPDATE)

`gr_whistleblower_reports`

GR-15: Whistleblower intake records — access restricted to compliance officer + org_admin; HR excluded RLS: ✅ enabled (3 policies)

Column	Type	Nullable	Default	Comment
`id`	uuid	no	gen_random_uuid()
`organization_id`	uuid	no
`report_token`	text	yes
`is_anonymous`	boolean	no	false
`reporter_profile_id`	uuid	yes
`category`	text	no
`description`	text	no
`status`	text	no	’received’::text
`assigned_to`	uuid	yes
`resolution_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:

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

Policies:

gr_whistleblower_insert (INSERT)
gr_whistleblower_select (SELECT)
gr_whistleblower_update (UPDATE)

Forms & Workflow Schema Reference Human Resources Schema Reference

​Entity relationships

​Tables

​gr_accreditation_audit_links

​gr_accreditation_caps

​gr_accreditation_compliance_links

​gr_accreditation_evidence

​gr_accreditation_mock_surveys

​gr_accreditation_standards

​gr_accreditation_surveys

​gr_accreditation_tracer_packs

​gr_accreditations

​gr_ai_audit_prep

​gr_ai_compliance_suggestions

​gr_ai_gap_analyses

​gr_ai_risk_assessments

​gr_audit_checklists

​gr_audit_corrective_actions

​gr_audit_evidence

​gr_audit_findings

​gr_audit_team_assignments

​gr_audits

​gr_benchmarking_aggregates

​gr_benchmarking_consents

​gr_board_minutes

​gr_board_resolutions

​gr_ceu_credits

​gr_coi_attestations

​gr_compliance_checks

​gr_compliance_evidence

​gr_compliance_remediation

​gr_compliance_submissions

​gr_contract_alerts

​gr_contract_amendments

​gr_contract_milestones

​gr_contract_obligations

​gr_contract_types

​gr_contracts

​gr_holiday_calendar

​gr_incident_corrective_actions

​gr_incident_findings

​gr_incident_investigations

​gr_incident_regulatory_reports

​gr_incident_risk_links

​gr_incidents

​gr_inservice_reminder_log

​gr_module_settings

​gr_policies

​gr_policy_acknowledgments

​gr_policy_assignments

​gr_policy_regulatory_links

​gr_policy_versions

​gr_procedure_compliance_score_snapshots

​gr_procedure_executions

​gr_procedure_policy_links

​gr_procedure_steps

​gr_procedure_template_regulatory_bodies

​gr_procedure_templates

​gr_procedure_versions

​gr_procedures

​gr_qi_improvements

​gr_qi_metric_measurements

​gr_qi_metrics

​gr_qi_pdsa_cycles

​gr_qi_project_templates

​gr_qi_projects

​gr_regulatory_bodies

​gr_regulatory_change_events

​gr_regulatory_change_impacts

​gr_regulatory_change_snapshots

​gr_regulatory_reporting_rules

​gr_regulatory_requirements

​gr_regulatory_sources

​gr_risk_assessments

​gr_risk_links

​gr_risk_mitigations

​gr_risks

​gr_state_kb_collections

​gr_state_kb_documents

​gr_training_completions

​gr_training_courses

Entity relationships

Tables

`gr_accreditation_audit_links`

`gr_accreditation_caps`

`gr_accreditation_compliance_links`

`gr_accreditation_evidence`

`gr_accreditation_mock_surveys`

`gr_accreditation_standards`

`gr_accreditation_surveys`

`gr_accreditation_tracer_packs`

`gr_accreditations`

`gr_ai_audit_prep`

`gr_ai_compliance_suggestions`

`gr_ai_gap_analyses`

`gr_ai_risk_assessments`

`gr_audit_checklists`

`gr_audit_corrective_actions`

`gr_audit_evidence`

`gr_audit_findings`

`gr_audit_team_assignments`

`gr_audits`

`gr_benchmarking_aggregates`

`gr_benchmarking_consents`

`gr_board_minutes`

`gr_board_resolutions`

`gr_ceu_credits`

`gr_coi_attestations`

`gr_compliance_checks`

`gr_compliance_evidence`

`gr_compliance_remediation`

`gr_compliance_submissions`

`gr_contract_alerts`

`gr_contract_amendments`

`gr_contract_milestones`

`gr_contract_obligations`

`gr_contract_types`

`gr_contracts`

`gr_holiday_calendar`

`gr_incident_corrective_actions`

`gr_incident_findings`

`gr_incident_investigations`

`gr_incident_regulatory_reports`

`gr_incident_risk_links`

`gr_incidents`

`gr_inservice_reminder_log`

`gr_module_settings`

`gr_policies`

`gr_policy_acknowledgments`

`gr_policy_assignments`

`gr_policy_regulatory_links`

`gr_policy_versions`

`gr_procedure_compliance_score_snapshots`

`gr_procedure_executions`

`gr_procedure_policy_links`

`gr_procedure_steps`

`gr_procedure_template_regulatory_bodies`

`gr_procedure_templates`

`gr_procedure_versions`

`gr_procedures`

`gr_qi_improvements`

`gr_qi_metric_measurements`

`gr_qi_metrics`

`gr_qi_pdsa_cycles`

`gr_qi_project_templates`

`gr_qi_projects`

`gr_regulatory_bodies`

`gr_regulatory_change_events`

`gr_regulatory_change_impacts`

`gr_regulatory_change_snapshots`

`gr_regulatory_reporting_rules`

`gr_regulatory_requirements`

`gr_regulatory_sources`

`gr_risk_assessments`

`gr_risk_links`

`gr_risk_mitigations`

`gr_risks`

`gr_state_kb_collections`

`gr_state_kb_documents`

`gr_training_completions`

`gr_training_courses`