Governance & Risk Database Tables

​

Overview

​

Table Categories

​

1. Policy Management (5 tables)

draft → review → approved → published → archived
                    ↓
              acknowledgments required

• title, description, content
• category, department_id
• effective_date, review_date, expiration_date
• owner_id, approver_id
• requires_acknowledgment

​

2. Risk Management (4 tables)

           Impact
           Low  Med  High  Critical
Likelihood ┌────┬────┬────┬────────┐
  Rare     │ 1  │ 2  │ 3  │   4    │
  Unlikely │ 2  │ 4  │ 6  │   8    │
  Possible │ 3  │ 6  │ 9  │  12    │
  Likely   │ 4  │ 8  │ 12 │  16    │
  Almost   │ 5  │ 10 │ 15 │  20    │
           └────┴────┴────┴────────┘

• strategic - Strategic risks
• operational - Operational risks
• financial - Financial risks
• compliance - Compliance risks
• reputational - Reputational risks
• technology - Technology/cyber risks

​

3. Compliance Management (4 tables)

• compliant - Meets requirements
• non_compliant - Does not meet requirements
• partial - Partially compliant
• not_applicable - N/A for organization
• under_review - Assessment in progress

​

4. Regulatory Framework (2 tables)

• HIPAA (Healthcare)
• OSHA (Workplace Safety)
• State Licensing Boards
• CARF/Joint Commission (Accreditation)
• CMS (Medicare/Medicaid)

​

5. Contract Management (6 tables)

• draft → negotiation → pending_approval → active
• expired, terminated, renewed

• Vendor/Supplier Agreements
• Client Service Agreements
• Employment Contracts
• Lease Agreements
• Insurance Policies
• Grant Agreements

​

6. Audit Management (6 tables)

• internal - Internal audit
• external - External audit
• regulatory - Regulatory inspection
• accreditation - Accreditation survey
• financial - Financial audit

• observation - Minor observation
• minor - Minor finding
• major - Major finding
• critical - Critical finding

​

7. Accreditation (6 tables)

• CARF (Commission on Accreditation of Rehabilitation Facilities)
• Joint Commission
• NCQA (National Committee for Quality Assurance)
• State Certification Programs

​

8. Training Management (5 tables)

• required - Mandatory compliance training
• role_based - Role-specific training
• optional - Professional development
• remedial - Corrective training

​

9. Quality Improvement (6 tables)

Plan → Do → Study → Act → (repeat)

​

10. AI Assistance (4 tables)

​

11. Module Settings (1 table)

​

Common Query Patterns

​

Get Risk Register

const { data } = await supabase
  .from('gr_risks')
  .select(`
    *,
    assessments:gr_risk_assessments(
      likelihood,
      impact,
      risk_score,
      assessed_at
    ),
    mitigations:gr_risk_mitigations(
      title,
      status,
      due_date
    )
  `)
  .eq('organization_id', orgId)
  .eq('status', 'active')
  .order('risk_score', { ascending: false });

​

Get Policies Requiring Acknowledgment

const { data } = await supabase
  .from('gr_policies')
  .select(`
    *,
    acknowledged:gr_policy_acknowledgments(
      acknowledged_at
    )
  `)
  .eq('status', 'published')
  .eq('requires_acknowledgment', true)
  .is('acknowledged.user_id', userId);

​

Get Upcoming Contract Renewals

const { data } = await supabase
  .from('gr_contracts')
  .select(`
    *,
    type:gr_contract_types(name),
    alerts:gr_contract_alerts(alert_type, alert_date)
  `)
  .eq('organization_id', orgId)
  .eq('status', 'active')
  .lte('end_date', addDays(new Date(), 90));

​

Get Audit Findings Summary

const { data } = await supabase
  .from('gr_audit_findings')
  .select(`
    severity,
    status,
    audit:gr_audits(title, audit_type)
  `)
  .eq('organization_id', orgId)
  .neq('status', 'closed');

​

Get Training Compliance

const { data } = await supabase
  .from('gr_training_courses')
  .select(`
    *,
    completions:gr_training_completions(
      employee_id,
      completed_at,
      expiration_date
    )
  `)
  .eq('organization_id', orgId)
  .eq('is_required', true);

​

RLS Policies

• Policy access based on assignments
• Risk visibility by department/role
• Audit access for audit team members

• gr_user_can_view_policy() - Policy access check
• gr_user_can_manage_risk() - Risk management access
• gr_user_is_audit_team_member() - Audit access check

​

Cross-Module Integration

gr_training_completions → hr_employee_credentials
gr_policy_acknowledgments → hr_employees

gr_contracts → fa_vendors (vendor contracts)
gr_contracts → fa_purchase_orders (contract-based POs)

​

See Also

• GR Module Specs
• Compliance Tracking Spec
• Database Schema Overview