Event Contracts

interface DomainEvent {
  event: string;              // Event name (e.g., 'rh_resident_admitted')
  publisher: string;           // Core that publishes (e.g., 'RH')
  subscriber: string[];        // Cores that subscribe (e.g., ['FA', 'HR'])
  payload: Record<string, any>; // Event-specific data
  metadata: {
    organization_id: uuid;
    site_id?: uuid;
    user_id: uuid;
    timestamp: timestamp;
    correlation_id?: uuid;
  };
}

Database Trigger → publish_domain_event() → pg_notify(channel, payload)
  → Edge Function Listener → Process Event → Log to Audit
  → Future: Trigger Notifications/Automations/Webhooks

interface DiagramBroadcastPayload {
  type: 'diagram_updated';
  version: number;
  userId: string;
  displayName: string;
  timestamp: string;
}

{
  trigger_type: 'form_submitted' | 'form_updated';
  submission_id: uuid;
  form_id: uuid;
  organization_id: uuid;
  site_id?: uuid;
  submitted_by: uuid;
  submission_data: Record<string, any>;
  old_status?: submission_status;  // Only for updates
  new_status: submission_status;
}

CREATE TRIGGER trigger_automation_on_submission
AFTER INSERT OR UPDATE ON fw_form_submissions
FOR EACH ROW
EXECUTE FUNCTION trigger_automation_on_submission();

{
  event_type: 'hr_credential_expired'; // Canonical name; legacy 'credential_expired' deprecated
  employee_id: uuid;
  credential_id: uuid;
  credential_type_id: uuid;
  expiration_date: date;
  organization_id: uuid;
  timestamp: timestamptz;
}

CREATE TRIGGER trigger_credential_expired
AFTER UPDATE ON hr_employee_credentials
FOR EACH ROW
WHEN (NEW.expiration_date < CURRENT_DATE AND 
      (OLD.expiration_date IS NULL OR OLD.expiration_date >= CURRENT_DATE))
EXECUTE FUNCTION publish_credential_expired();
-- Note: Function should publish as 'hr_credential_expired' (canonical), legacy name maintained for backward compatibility

{
  event_type: 'hr_credential_verified'; // Canonical name; legacy 'credential_verified' deprecated
  employee_id: uuid;
  credential_id: uuid;
  credential_type_id: uuid;
  verified_by: uuid;
  verified_at: timestamptz;
  organization_id: uuid;
  timestamp: timestamptz;
}

CREATE TRIGGER trigger_credential_verified
AFTER UPDATE ON hr_employee_credentials
FOR EACH ROW
WHEN (NEW.verification_status = 'verified' AND OLD.verification_status != 'verified')
EXECUTE FUNCTION publish_credential_verified();
-- Note: Function should publish as 'hr_credential_verified' (canonical), legacy name maintained for backward compatibility

-- HR-04: Block employee from scheduling when credential expires
CREATE OR REPLACE FUNCTION hr_block_employee_on_credential_expiry()
RETURNS TRIGGER AS $$
BEGIN
  -- Update employee scheduling eligibility
  UPDATE hr_employees
  SET scheduling_blocked = true,
      scheduling_block_reason = 'credential_expired', // Uses legacy event name for backward compatibility
      scheduling_blocked_at = now()
  WHERE id = NEW.employee_id
    AND organization_id = NEW.organization_id;
  
  RETURN NEW;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

-- Subscribe to credential_expired events via edge function
-- Location: supabase/functions/hr-scheduling-credential-listener/index.ts

{
  event_type: 'hr_onboarding_completed'; // legacy: 'onboarding_completed'
  employee_id: uuid;
  onboarding_instance_id: uuid;
  completed_at: timestamptz;
  organization_id: uuid;
  timestamp: timestamptz;
}

{
  event_type: 'offboarding_completed';
  employee_id: uuid;
  offboarding_instance_id: uuid;
  completed_at: timestamptz;
  termination_type: termination_type;
  organization_id: uuid;
  timestamp: timestamptz;
}

{
  event_type: 'hr_onboarding_started';
  employee_id: uuid;
  onboarding_instance_id: uuid;
  template_id: uuid;
  start_date: date;
  is_clinical: boolean;
  organization_id: uuid;
  timestamp: timestamptz;
}

{
  event_type: 'hr_onboarding_task_created';
  task_instance_id: uuid;
  onboarding_instance_id: uuid;
  employee_id: uuid;
  category: 'it_setup';
  title: string;
  due_date: date;
  organization_id: uuid;
  timestamp: timestamptz;
}

{
  event_type: 'hr_offboarding_started';
  employee_id: uuid;
  offboarding_instance_id: uuid;
  termination_date: date;
  termination_type: 'voluntary' | 'involuntary' | 'retirement' | 'end_contract';
  organization_id: uuid;
  timestamp: timestamptz;
}

{
  event_type: 'hr_offboarding_task_created';
  checklist_item_id: uuid;
  offboarding_instance_id: uuid;
  employee_id: uuid;
  category: 'access_revocation';
  title: string;
  organization_id: uuid;
  timestamp: timestamptz;
}

{
  event_type: 'hr_policy_acknowledgment_completed';
  acknowledgment_id: uuid;
  employee_id: uuid;
  policy_id: uuid;
  distribution_id: uuid;
  organization_id: uuid;
  acknowledged_at: timestamptz;
  timestamp: timestamptz;
}

{
  event_type: 'hr_policy_distribution_created';
  distribution_id: uuid;
  policy_id: uuid;
  policy_title: string;
  employee_ids: uuid[];
  deadline_date: date;
  organization_id: uuid;
  timestamp: timestamptz;
}

interface HrTimesheetExceptionCreatedPayload {
  event_type: 'hr_timesheet_exception_created';
  exception_id: string;       // UUID
  employee_id: string;        // UUID
  exception_type: string;
  exception_date: string;     // ISO date
  organization_id: string;    // UUID — required for tenant isolation
  site_id?: string;           // UUID — optional site context
  timestamp: string;          // ISO 8601 timestamptz
  correlation_id?: string;    // UUID — optional for tracing
}

{
  event_type: 'performance_review_completed';
  review_id: uuid;
  employee_id: uuid;
  reviewer_id: uuid;
  overall_rating: 'exceeds_expectations' | 'meets_expectations' | 'below_expectations';
  review_period_start: date;
  review_period_end: date;
  organization_id: uuid;
  timestamp: timestamptz;
}

CREATE TRIGGER trigger_performance_review_completed
  AFTER UPDATE ON public.hr_performance_reviews
  FOR EACH ROW
  EXECUTE FUNCTION public.hr_trigger_performance_review_completed();

{
  event_type: 'performance_goal_completed';
  goal_id: uuid;
  employee_id: uuid;
  organizational_goal_id: uuid | null;
  status: 'completed' | 'not_met';
  organization_id: uuid;
  timestamp: timestamptz;
}

{
  event_type: 'hr_pip_terminated';
  pip_id: uuid;
  employee_id: uuid;
  organization_id: uuid;
  disciplinary_action_id: uuid;  // Created HR-14 record
  final_assessment: string;
  terminated_at: timestamptz;
}

{
  event: 'hr.benefit_enrollment.created';
  payload: {
    employee_id: uuid;
    benefit_enrollment_id: uuid;
    amount: number;           // employee contribution amount for deduction
    pre_tax: boolean;
  };
  metadata: {
    organization_id: uuid;
    user_id?: uuid;
    timestamp: timestamptz;
    correlation_id?: uuid;
  };
}

{
  event: 'hr.benefit_enrollment.terminated';
  payload: {
    employee_id: uuid;
    benefit_enrollment_id: uuid;
    amount: number;           // last contribution amount (for reference)
    pre_tax: boolean;
  };
  metadata: {
    organization_id: uuid;
    user_id?: uuid;
    timestamp: timestamptz;
    correlation_id?: uuid;
  };
}

{
  event: 'hr.employer_tax_liability.calculated';
  payload: {
    organization_id: uuid;
    tax_period_type: 'quarterly' | 'annual';
    tax_period_start: date;
    tax_period_end: date;
    federal_income_tax_withheld: number;
    social_security_tax_withheld: number;
    medicare_tax_withheld: number;
    employer_ss_tax: number;
    employer_medicare_tax: number;
    additional_medicare_tax_withheld?: number;
    total_liability: number;
    tax_form_run_id?: uuid;
  };
  metadata: {
    organization_id: uuid;
    user_id?: uuid;
    timestamp: timestamptz;
    correlation_id?: uuid;
  };
}

{
  event: 'hr_contractor_created';
  payload: {
    organization_id: uuid;
    contractor_id: uuid;
    classification: string; // 'independent_contractor' | 'agency_contractor' | 'consultant' | 'temp_worker'
    agency_id?: uuid; // if agency_contractor
  };
  metadata: {
    organization_id: uuid;
    user_id: uuid;
    timestamp: timestamptz;
    correlation_id?: uuid;
  };
}

{
  event: 'hr_contractor_contract_renewal_due';
  payload: {
    organization_id: uuid;
    contractor_id: uuid;
    contract_id: uuid;
    end_date: date;
    renewal_notice_days: number; // days until expiration
  };
  metadata: {
    organization_id: uuid;
    timestamp: timestamptz;
    correlation_id?: uuid;
  };
}

{
  event: 'hr_contractor_credential_expiring';
  payload: {
    organization_id: uuid;
    contractor_id: uuid;
    credential_id: uuid;
    expiration_date: date;
    days_until_expiration: number;
  };
  metadata: {
    organization_id: uuid;
    timestamp: timestamptz;
    correlation_id?: uuid;
  };
}

interface ExpenseReportSubmittedEvent {
  event_version: '1.0';
  event_id: string;
  event_timestamp: string;
  organization_id: string;
  expense_report_id: string;
  employee_id: string;
  total_amount: number;
  submitted_at: string;
  submitted_by: string;
  line_count: number;
  has_policy_violations: boolean;
}

{
  event_type: 'fa_expense_report_approved'; // Canonical name
  event_id: uuid;
  event_version: '1.0';
  expense_report_id: uuid;
  report_number: string;
  employee_id: uuid;
  total_amount: number;
  approved_by: uuid;
  approved_at: timestamptz;
  approval_id: uuid;
  organization_id: uuid;
  timestamp: timestamptz;
}

{
  event_type: 'fa_expense_report_rejected'; // Canonical name
  event_id: uuid;
  event_version: '1.0';
  expense_report_id: uuid;
  report_number: string;
  employee_id: uuid;
  rejected_at: timestamptz;
  rejected_by: uuid;
  rejection_reason: string;
  organization_id: uuid;
  timestamp: timestamptz;
}

{
  event_type: 'fa_reimbursement_processed'; // Canonical name
  event_id: uuid;
  event_version: '1.0';
  reimbursement_payment_id: uuid;
  payment_number: string;
  employee_id: uuid;
  payment_amount: number;
  journal_entry_id: uuid;
  processed_by: uuid;
  processed_at: timestamptz;
  organization_id: uuid;
  timestamp: timestamptz;
}

{
  event_type: 'disciplinary_action_termination';
  disciplinary_action_id: uuid;
  employee_id: uuid;
  action_type: 'termination';
  termination_reason: string;
  organization_id: uuid;
  timestamp: timestamptz;
}

{
  event_type: 'incident_reported';
  incident_id: uuid;
  incident_type: 'accident' | 'injury' | 'safety_violation' | 'property_damage' | 'other';
  severity: 'minor' | 'moderate' | 'serious' | 'critical';
  incident_date: date;
  organization_id: uuid;
  site_id?: uuid;
  timestamp: timestamptz;
}

{
  event_type: 'grievance_filed';
  grievance_id: uuid;
  employee_id: uuid;
  grievance_type: 'harassment' | 'discrimination' | 'wage_dispute' | 'working_conditions' | 'other';
  filed_date: date;
  organization_id: uuid;
  timestamp: timestamptz;
}

{
  case_id: uuid;
  employee_id: uuid;
  uscis_case_number: string;
  organization_id: uuid;
  timestamp: timestamptz;
}

{
  case_id: uuid;
  employee_id: uuid;
  old_status: string;
  new_status: string;
  organization_id: uuid;
  timestamp: timestamptz;
}

{
  case_id: uuid;
  employee_id: uuid;
  tnc_type: string;
  organization_id: uuid;
  timestamp: timestamptz;
}

{
  event_type: 'merit_increase_approved';
  merit_increase_id: uuid;
  employee_id: uuid;
  current_salary: number;
  new_salary: number;
  increase_percentage: number;
  effective_date: date;
  organization_id: uuid;
  timestamp: timestamptz;
}

{
  event_type: 'compensation_analysis_completed';
  analysis_id: uuid;
  analysis_type: 'internal_equity' | 'market_comparison' | 'distribution';
  disparity_percent: number;
  organization_id: uuid;
  timestamp: timestamptz;
}

{
  event_type: 'compensation_cost_allocated';
  source: 'hr_merit_increases';
  merit_increase_id: uuid;
  employee_id: uuid;
  employee_name: string;
  department_id: uuid | null;
  department_name: string | null;
  position_title: string | null;
  previous_salary: number;
  new_salary: number;
  increase_amount: number;
  increase_percentage: number;
  annual_cost_impact: number;
  effective_date: date;
  currency: string;
  organization_id: uuid;
  approved_by: uuid;
  approved_at: timestamptz;
  timestamp: timestamptz;
}

{
  event_type: 'successor_identified';
  succession_plan_id: uuid;
  position_id: uuid;
  successor_id: uuid;
  readiness_level: 'ready_now' | 'ready_1_year' | 'ready_2_years' | 'ready_3_plus_years';
  organization_id: uuid;
  timestamp: timestamptz;
}

{
  event_type: 'talent_promoted';
  employee_id: uuid;
  from_position_id: uuid;
  to_position_id: uuid;
  promotion_date: date;
  organization_id: uuid;
  timestamp: timestamptz;
}

{
  event_type: 'survey_completed';
  survey_id: uuid;
  response_id: uuid;
  employee_id: uuid | null; // null if anonymous
  completion_percentage: number;
  organization_id: uuid;
  timestamp: timestamptz;
}

{
  event_type: 'exit_interview_completed';
  exit_interview_id: uuid;
  employee_id: uuid;
  offboarding_instance_id: uuid;
  reason_for_leaving: string;
  would_recommend: boolean;
  organization_id: uuid;
  timestamp: timestamptz;
}

{
  event_type: 'hr_proliant_sync_completed';
  sync_run_id: uuid;
  organization_id: uuid;
  direction: 'pull' | 'push';
  scope: string;                // e.g., "employees", "leave_balances", "payroll"
  status: 'completed' | 'partial' | 'failed';
  records_processed: number;
  records_failed: number;
  conflicts_detected: number;
  timestamp: timestamptz;
}

{
  event_type: 'hr_proliant_conflict_detected';
  sync_run_id: uuid;
  organization_id: uuid;
  employee_id: uuid;
  field_name: string;           // e.g., "personal_phone", "manager_id", "is_exempt"
  conflict_log_id: uuid;        // Reference to hr_proliant_conflict_logs
  timestamp: timestamptz;
}

{
  event_type: 'hr_payroll_run_started';
  organization_id: uuid;
  payroll_run_id: uuid;      // Encore-side run id
  calendar_id: string;       // Proliant pay-calendar id
  timestamp: timestamptz;
}

{
  event_type: 'hr_payroll_run_preprocessed';
  organization_id: uuid;
  payroll_run_id: uuid;
  calendar_id: string;
  employee_count: number;    // aggregate; non-identifying
  gross_total?: number;      // aggregate only
  timestamp: timestamptz;
}

{
  event_type: 'hr_payroll_run_verified';
  organization_id: uuid;
  payroll_run_id: uuid;
  calendar_id: string;
  verification_result: 'passed' | 'failed' | 'partial';
  checks_total: number;
  checks_failed: number;
  timestamp: timestamptz;
}

{
  event_type: 'hr_payroll_run_approved';
  organization_id: uuid;
  payroll_run_id: uuid;
  calendar_id: string;
  batch_id: string;
  approved_by_user_id: uuid;
  timestamp: timestamptz;
}

{
  run_state_id: string;        // uuid — hr_proliant_run_state.id (Encore-side run)
  organization_id: string;     // uuid — tenant
  calendar_guid: string;       // Proliant pay-calendar GUID
  entry_date: string | null;   // ISO date (YYYY-MM-DD) from Proliant JE; null if not stamped
  lines: Array<{
    fa_account_id: string;     // uuid — resolved Encore FA account
    debit: number;
    credit: number;
    gl_code: string;           // Proliant gl-segment code (preserved for audit)
  }>;
}

{
  event_type: 'fa_payroll_je_generated';
  organization_id: uuid;
  payroll_run_id: uuid;       // links back to the HR run
  journal_entry_id: uuid;     // draft fa_journal_entries row
  source_type: 'PAYROLL';
  line_count: number;
  total_debit: number;        // aggregate
  total_credit: number;       // aggregate
  timestamp: timestamptz;
}

{
  bill_id: uuid;
  bill_number: string;
  vendor_id: uuid;
  vendor_name: string;
  total_amount: number;
  due_date: date;
  approved_by: uuid;
  approved_at: timestamp;
  approval_workflow_id?: uuid;
  organization_id: uuid;
  site_id?: uuid;
}

{
  payment_id: uuid;
  payment_number: string;
  vendor_id: uuid;
  payment_amount: number;
  payment_date: date;
  payment_method: 'check' | 'ach' | 'wire' | 'card';
  check_number?: string;
  bank_account_id: uuid;
  applied_bills: [{
    bill_id: uuid;
    amount_applied: number;
  }];
  organization_id: uuid;
  processed_by: uuid;
  processed_at: timestamp;
}

publish_domain_event('fa.vendor.activated', { version: '1.0.0', payload });

{
  vendor_id: uuid;
  organization_id: uuid;
  created_by_user_id: uuid;
  classification: '1099-MISC' | '1099-NEC' | null; // public 1099 categorization only; no TIN/bank data
  has_portal_invite: boolean;
  is_active: boolean;
  notes?: string; // optional; non-sensitive summary only — must NOT include vendor contact details (emails, phone numbers), TIN or partial TIN fragments, bank account/routing numbers or hints, insurance policy numbers, health/medical identifiers, or any other PII/PHI/secrets subject to compliance
}

{
  event_type: 'fa_bill_scan_completed';
  organization_id: uuid;      // Required
  timestamp: timestamptz;     // Required
  user_id?: uuid;            // When user-initiated (e.g. scan from UI)
  correlation_id?: string;   // When chaining with other events
  scan_id: uuid;
  vendor_bill_id: uuid | null;  // Set when linked to bill
  confidence_score: number;
}

{
  event_type: 'fa_bill_duplicate_flagged';
  organization_id: uuid;     // Required
  timestamp: timestamptz;     // Required
  user_id?: uuid;            // When user-initiated
  correlation_id?: string;   // When chaining (e.g. from same batch)
  vendor_bill_id: uuid;
  suspected_duplicate_of_id: uuid;
  match_score: number;
  match_reason: string;
  log_id: uuid;
}

{
  event_type: 'purchase_order_approved';
  po_id: uuid;
  po_number: string;
  vendor_id: uuid;
  po_total: number;
  approved_by: uuid;
  approved_at: timestamp;
  user_id: uuid;  // Mirrors approved_by for user-initiated actions
  organization_id: uuid;
  site_id?: uuid;
  timestamp: timestamptz;
}

{
  event_type: 'goods_received';
  receipt_id: uuid;
  receipt_number: string;
  po_id: uuid;
  po_number: string;
  receipt_date: date;
  received_by: uuid;
  user_id: uuid;  // Mirrors received_by for user-initiated actions
  organization_id: uuid;
  site_id?: uuid;
  timestamp: timestamptz;
}

{
  journal_entry_id: uuid;
  entry_number: string;
  entry_date: date;
  description: string;
  total_debit: number;
  total_credit: number;
  status: 'posted';
  source: 'manual' | 'accounts_payable' | 'payroll' | 'accounts_receivable' | 'bank_import';
  source_document_id?: uuid;
  fiscal_period_id: uuid;
  organization_id: uuid;
  posted_by: uuid;
  posted_at: timestamp;
  lines: [{
    account_id: uuid;
    fund_id?: uuid;
    department_id?: uuid;
    debit: number;
    credit: number;
  }];
}

{
  budget_id: uuid;
  budget_name: string;
  fiscal_year: number;
  fund_id?: uuid;
  department_id?: uuid;
  total_amount: number;
  approved_by: uuid;
  approved_at: timestamp;
  approval_level: number;
  organization_id: uuid;
}

{
  budget_id: uuid;
  budget_name: string;
  account_id: uuid;
  account_number: string;
  fund_id?: uuid;
  department_id?: uuid;
  budget_amount: number;
  actual_amount: number;
  variance_percent: number;
  threshold_type: 'warning' | 'critical';
  organization_id: uuid;
  triggered_by_entry_id: uuid;
  triggered_at: timestamp;
}

{
  consolidation_id: uuid;
  consolidation_name: string;
  as_of_date: date;
  entities_included: uuid[];
  total_eliminations: number;
  elimination_amount: number;
  consolidated_assets: number;
  consolidated_liabilities: number;
  consolidated_net_assets: number;
  parent_organization_id: uuid;
  completed_by: uuid;
  completed_at: timestamp;
}

{
  "event_type": "pm_time_off_requested",
  "schema_version": 1,
  "payload": {
    "organization_id": "uuid",
    "provider_id": "uuid",
    "time_off_id": "uuid",
    "start_date": "YYYY-MM-DD",
    "end_date": "YYYY-MM-DD",
    "time_off_type": "vacation | sick | personal | continuing_education | conference | other"
  }
}

{
  "event_type": "pm_time_off_approved",
  "schema_version": 1,
  "payload": {
    "organization_id": "uuid",
    "provider_id": "uuid",
    "time_off_id": "uuid",
    "approved_by": "uuid",
    "coverage_provider_id": "uuid | null"
  }
}

{
  "event_type": "pm_time_off_denied",
  "schema_version": 1,
  "payload": {
    "organization_id": "uuid",
    "provider_id": "uuid",
    "time_off_id": "uuid",
    "denied_by": "uuid"
  }
}

{
  "event_type": "cl_marketplace_bundle_imported",
  "schema_version": 1,
  "payload": {
    "event_id": "uuid",
    "organization_id": "uuid",
    "import_id": "uuid",
    "bundle_id": "uuid",
    "bundle_key": "string",
    "bundle_version": "integer",
    "imported_item_count": "integer",
    "unsupported_item_count": "integer"
  }
}

{
  event_type: 'action_unblocked';
  action_id: uuid;          // The action that was unblocked
  goal_id: uuid;            // The parent goal (rock_id)
  organization_id: uuid;
  site_id?: uuid;
  dependency_action_id: uuid; // The action that was completed
  unblocked_at: timestamptz;
  unblocked_by_user_id?: uuid; // User who completed the dependency
  timestamp: timestamptz;
}

-- Trigger when an action completes that has dependent actions
CREATE OR REPLACE FUNCTION lo_publish_action_unblocked()
RETURNS TRIGGER AS $$
DECLARE
  v_dependent_action RECORD;
BEGIN
  -- Only fire when action is completed
  IF NEW.status = 'complete' AND (OLD.status IS NULL OR OLD.status != 'complete') THEN
    -- Find actions that depend on this action
    FOR v_dependent_action IN
      SELECT id, rock_id, organization_id
      FROM lo_rock_milestones
      WHERE depends_on_action_id = NEW.id
    LOOP
      PERFORM pg_notify('lo_events', json_build_object(
        'event_type', 'action_unblocked',
        'action_id', v_dependent_action.id,
        'goal_id', v_dependent_action.rock_id,
        'organization_id', v_dependent_action.organization_id,
        'dependency_action_id', NEW.id,
        'unblocked_at', now(),
        'unblocked_by_user_id', auth.uid(),
        'timestamp', now()
      )::text);
    END LOOP;
  END IF;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER SET search_path = public;

{
  event_type: 'action_status_changed';
  action_id: uuid;
  goal_id: uuid;            // The parent goal (rock_id)
  organization_id: uuid;
  site_id?: uuid;
  old_status: 'not_started' | 'in_progress' | 'blocked' | 'on_hold' | 'needs_review' | 'complete';
  new_status: 'not_started' | 'in_progress' | 'blocked' | 'on_hold' | 'needs_review' | 'complete';
  changed_by: uuid;
  changed_at: timestamptz;
  timestamp: timestamptz;
}

CREATE OR REPLACE FUNCTION lo_publish_action_status_changed()
RETURNS TRIGGER AS $$
BEGIN
  IF OLD.status IS DISTINCT FROM NEW.status THEN
    PERFORM pg_notify('lo_events', json_build_object(
      'event_type', 'action_status_changed',
      'action_id', NEW.id,
      'goal_id', NEW.rock_id,
      'organization_id', NEW.organization_id,
      'old_status', COALESCE(OLD.status, 'not_started'),
      'new_status', NEW.status,
      'changed_by', auth.uid(),
      'changed_at', now(),
      'timestamp', now()
    )::text);
  END IF;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER SET search_path = public;

CREATE TRIGGER trigger_action_status_changed
AFTER UPDATE ON lo_rock_milestones
FOR EACH ROW
WHEN (OLD.status IS DISTINCT FROM NEW.status)
EXECUTE FUNCTION lo_publish_action_status_changed();

{
  event_type: 'execution_failed';
  organization_id: uuid;
  site_id?: uuid;
  execution_id: uuid;
  workflow_id: uuid;
  workflow_name: string;
  failed_node_id?: string;
  failed_node_name?: string;
  error_message: string;
  error_code?: string;
  started_at: timestamptz;
  failed_at: timestamptz;
  trigger_type: 'form_submitted' | 'form_updated' | 'scheduled' | 'manual' | 'event';
  triggered_by?: uuid;
  timestamp: timestamptz;
}

CREATE TRIGGER trigger_execution_failed
AFTER UPDATE ON fw_workflow_executions
FOR EACH ROW
WHEN (NEW.status = 'failed' AND OLD.status != 'failed')
EXECUTE FUNCTION publish_execution_failed();

{
  event_type: 'fw_event_schema_updated';
  organization_id: uuid;
  payload: {
    event_name: string;
    old_schema_version: number;
    new_schema_version: number;
    changed_by: uuid;
  };
}

{
  event_type: 'fw_event_deprecated';
  organization_id: uuid;
  payload: {
    event_name: string;
    deprecated_at: string;
    replacement_event: string | null;
    deprecated_by: uuid;
  };
}

{
  event_type: 'fw_external_form_submitted';
  organization_id: uuid;
  site_id?: uuid;
  form_id: uuid;
  submission_id: uuid;
  portal_config_id: uuid;
  submitted_at: timestamptz;
  submitted_by_email: string;
  verified: boolean;
  spam_score?: number;
  timestamp: timestamptz;
}

CREATE OR REPLACE FUNCTION fw_publish_external_form_submitted()
RETURNS TRIGGER AS $$
DECLARE
  v_portal_config fw_form_portal_config;
BEGIN
  -- Get portal config
  SELECT * INTO v_portal_config
  FROM fw_form_portal_config
  WHERE form_id = NEW.form_id
    AND organization_id = NEW.organization_id
    AND is_active = true
  LIMIT 1;
  
  IF v_portal_config IS NOT NULL THEN
    PERFORM pg_notify('fw_events', json_build_object(
      'event_type', 'fw_external_form_submitted',
      'organization_id', NEW.organization_id,
      'site_id', NEW.site_id,
      'form_id', NEW.form_id,
      'submission_id', NEW.id,
      'portal_config_id', v_portal_config.id,
      'submitted_at', NEW.submitted_at,
      'submitted_by_email', NEW.submitted_by_email,
      'verified', NEW.email_verified,
      'spam_score', NEW.spam_score,
      'timestamp', now()
    )::text);
  END IF;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

CREATE TRIGGER trigger_external_form_submitted
AFTER INSERT ON fw_form_submissions
FOR EACH ROW
WHEN (NEW.submitted_via = 'portal')
EXECUTE FUNCTION fw_publish_external_form_submitted();

{
  event_type: 'fw_approval_submitted';
  organization_id: uuid;
  site_id?: uuid;
  request_id: uuid;
  chain_id: uuid;
  title: string;
  submitted_by: uuid;
  source_type: 'form_submission' | 'entity' | 'workflow';
  source_id: uuid;
  priority: 'low' | 'normal' | 'high' | 'urgent';
  timestamp: timestamptz;
}

CREATE OR REPLACE FUNCTION fw_publish_approval_submitted()
RETURNS TRIGGER AS $$
BEGIN
  PERFORM pg_notify('fw_events', json_build_object(
    'event_type', 'fw_approval_submitted',
    'organization_id', NEW.organization_id,
    'request_id', NEW.id,
    'chain_id', NEW.chain_id,
    'title', NEW.title,
    'submitted_by', NEW.submitted_by,
    'source_type', NEW.source_type,
    'source_id', NEW.source_id,
    'priority', NEW.priority,
    'timestamp', now()
  )::text);
  RETURN NEW;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

CREATE TRIGGER trigger_approval_submitted
AFTER INSERT ON fw_approval_requests
FOR EACH ROW
EXECUTE FUNCTION fw_publish_approval_submitted();