HazCom Plan Builder Schema

Overview

The HazCom Plan Builder uses the plan_hazcom_ prefix for all tables. The schema supports two-tier plan creation (Basic + Premium), versioning, approval workflows, audit logging, and AI content generation via JSONB metadata.

Tables

Table	Purpose	Status
plan_hazcom_plans	Master plan records with versioning	Implemented
plan_hazcom_plan_sections	Section content per plan (7 OSHA sections)	Implemented
plan_hazcom_question_templates	Global question bank	Implemented
plan_hazcom_audit_logs	Dedicated audit trail	Implemented
plan_hazcom_pending_changes	Change detection alerts	Planned
plan_hazcom_review_tasks	Annual review tasks	Planned

---

plan_hazcom_plans

Master record for each HazCom plan version. One active plan per site enforced via partial unique index.

Key Columns

Column	Type	Description
plan_id	UUID, PK	Primary identifier
company_id	UUID, FK	Company ownership
site_id	UUID, FK	Site scope
plan_name	String(255)	Plan title
plan_type	String(30)	basic or premium
version_number	String(20)	Semantic version (e.g., "1.0", "2.0")
version_status	String(30)	draft, pending_approval, approved, active, archived

Coordinator Fields

Column	Type	Description
coordinator_user_id	UUID, FK	Links to users table
coordinator_name	String(255)	Coordinator name
coordinator_title	String(100)	Job title
coordinator_email	String(255)	Contact email
coordinator_phone	String(50)	Contact phone

Version Links

Column	Type	Description
previous_version_id	UUID, FK (self)	Links to prior version
superseded_by_id	UUID, FK (self)	Links to newer version
created_by_user_id	UUID, FK	User who created

Indexes

• ix_plan_hazcom_plans_company_site — Composite (company_id, site_id)
• ix_plan_hazcom_plans_status — B-tree on version_status
• ix_plan_hazcom_plans_unique_active — Partial unique: one active plan per site

Status Workflow

DRAFT → PENDING_APPROVAL → APPROVED → ACTIVE → ARCHIVED
                                          ↑
                              (Create New Version → new DRAFT)

---

plan_hazcom_plan_sections

Stores questionnaire answers and generated content for each of the 7 OSHA sections per plan.

Section Codes

Code	Title	OSHA Reference
company_info	Company & Site Information	General
inventory	Chemical Inventory	§1910.1200(e)
labeling	Container Labeling	§1910.1200(f)
sds	Safety Data Sheets	§1910.1200(g)
training	Employee Training	§1910.1200(h)
non_routine	Non-Routine Tasks	§1910.1200(e)(1)(ii)
contractors	Contractor Coordination	§1910.1200(e)(2)

Key Columns

Column	Type	Description
section_id	UUID, PK	Primary identifier
plan_id	UUID, FK	Parent plan
section_code	String(50)	Section identifier
section_order	Integer	Display order (1-7)
content_type	String(30)	manual, ai_generated, hybrid
questionnaire_answers	JSONB	User's answers to section questions
generated_content	Text	Final rendered section content (markdown)
content_metadata	JSONB	AI metadata, dynamic questions, prefill data
completion_percentage	Integer	0-100

AI Tracking Columns

Column	Type	Description
ai_generated_at	Timestamp	When AI content was generated
ai_model_version	String(50)	AI model identifier
user_edited	Boolean	True if user modified AI content

content_metadata JSONB Structure

Used by Phases 3-5 for AI operations:

{
  "dynamic_questions": [
    {
      "question_code": "dq_flammable_storage",
      "question_text": "How are flammable chemicals stored?",
      "question_type": "textarea",
      "section_code": "inventory",
      "is_required": true,
      "ai_generated": true
    }
  ],
  "prefill_data": {
    "question_code": {
      "suggested_answer": "Receiving personnel verify...",
      "confidence": 0.85,
      "source": "company_profile"
    }
  },
  "ai_task_id": "uuid",
  "ai_task_status": "questions_ready",
  "confidence_score": 0.92,
  "ai_contribution": {
    "ai_assisted_answer_count": 12,
    "total_answer_count": 25,
    "ai_generated_section_count": 7,
    "total_section_count": 7
  }
}

---

plan_hazcom_question_templates

Global question bank defining the questionnaire structure. Shared across all companies.

Key Columns

Column	Type	Description
template_id	UUID, PK	Primary identifier
section_code	String(50)	OSHA section
question_code	String(100)	Unique within section
question_order	Integer	Display order
question_text	Text	The question
question_type	String(30)	text, textarea, select, select_multiple, yes_no, date
options	JSONB	Options for select types (value + text + paragraph)
is_required	Boolean	Must be answered
ai_prefillable	Boolean	Can be pre-populated by AI
available_for_basic	Boolean	Show in Basic plans
available_for_premium	Boolean	Show in Premium plans

---

plan_hazcom_audit_logs

Dedicated audit trail for all plan actions. Implemented in Phase 2.

Key Columns

Column	Type	Description
log_id	UUID, PK	Primary identifier
plan_id	UUID, FK	Related plan
company_id	UUID, FK	Company scope
action	String(50)	Action type (16 types)
action_category	String(30)	general, section, workflow, export, version
action_details	JSONB	Structured action metadata
plan_version	String(20)	Version at time of action
plan_status	String(30)	Status at time of action
user_id	UUID, FK	User who performed action
user_email	String(255)	Denormalized for fast reads
user_ip	String(45)	IPv6-compatible IP
user_agent	String(500)	Browser user agent

Action Types

Action	Category
created	general
updated	general
deleted	general
viewed	general
section_updated	section
section_content_edited	section
submitted	workflow
approved	workflow
rejected	workflow
published	workflow
archived	version
exported_pdf	export
exported_docx	export
preview_generated	export
version_created	version
version_restored	version

---

Entity Relationships

core_data_companies ──┐
                      │
core_data_company_sites ──┐
                          │
                          v
              plan_hazcom_plans (Master)
                  │
      ┌───────────┼────────────┐
      v           v            v
  sections    audit_logs    (self-ref: previous_version)
  (7 per plan)

plan_hazcom_question_templates (Global, no FK to plans)

Related Documentation

• Product Overview — Feature overview and user-facing documentation
• Implementation Plan — Technical implementation details