Phase 1: Backend API Foundation for Work Instructions

[kevalyq]

📌 Parent Epic

#101 (Work Instructions Management)

🎯 Goal

Implement the foundational backend API for Work Instructions (Dienstanweisungen) management, including database schema, CRUD endpoints, template system, and standard text blocks library.

---

📋 Implementation Tasks

1. Database Schema

Tables:

// Migration: create_work_instructions_table
Schema::create('work_instructions', function (Blueprint $table) {
    $table->uuid('id')->primary();
    $table->string('instruction_number')->unique(); // e.g., "DA-2025-001"
    $table->string('title');
    $table->text('description')->nullable();
    $table->integer('version')->default(1);
    $table->json('sections'); // Array of section objects
    $table->date('valid_from');
    $table->date('valid_until')->nullable();
    $table->enum('scope', ['all_employees', 'specific_employees', 'by_role', 'by_location']);
    $table->json('scope_criteria')->nullable(); // Array of IDs/values
    $table->boolean('requires_acknowledgment')->default(true);
    $table->integer('acknowledgment_deadline_days')->default(14);
    $table->enum('status', ['draft', 'review', 'published', 'archived']);
    $table->foreignUuid('created_by')->constrained('users');
    $table->foreignUuid('updated_by')->nullable()->constrained('users');
    $table->timestamps();
    $table->softDeletes();
    
    $table->index(['status', 'valid_from']);
    $table->index('scope');
});

// Migration: create_work_instruction_templates_table
Schema::create('work_instruction_templates', function (Blueprint $table) {
    $table->uuid('id')->primary();
    $table->string('name');
    $table->text('description')->nullable();
    $table->enum('category', ['general', 'site_specific', 'safety', 'legal']);
    $table->json('sections'); // Template sections
    $table->boolean('is_system_template')->default(false); // Read-only
    $table->foreignUuid('tenant_id')->nullable()->constrained('tenants');
    $table->timestamps();
    
    $table->index(['category', 'is_system_template']);
});

// Migration: create_standard_blocks_table
Schema::create('standard_blocks', function (Blueprint $table) {
    $table->uuid('id')->primary();
    $table->string('name');
    $table->enum('category', ['dsgvo', 'reporting', 'fire_safety', 'first_aid', 'general']);
    $table->text('content_de'); // German content
    $table->text('content_en'); // English content
    $table->boolean('is_locked')->default(true); // Cannot be edited when inserted
    $table->integer('sort_order')->default(0);
    $table->timestamps();
    
    $table->index('category');
});

// Migration: create_work_instruction_acknowledgments_table
Schema::create('work_instruction_acknowledgments', function (Blueprint $table) {
    $table->uuid('id')->primary();
    $table->foreignUuid('work_instruction_id')->constrained('work_instructions');
    $table->foreignUuid('user_id')->constrained('users');
    $table->timestamp('acknowledged_at');
    $table->ipAddress('ip_address'); // GDPR: Record for audit trail
    $table->string('user_agent'); // Browser info
    $table->text('notes')->nullable(); // Optional employee comments
    $table->timestamps();
    
    $table->unique(['work_instruction_id', 'user_id']);
    $table->index('acknowledged_at');
});

---

2. Models

• WorkInstruction model with relationships
• WorkInstructionTemplate model
• StandardBlock model
• WorkInstructionAcknowledgment model
• Add scopes for filtering (published, draft, archived)
• Add accessor for localized standard block content

---

3. API Endpoints

Work Instructions CRUD

• GET /api/v1/work-instructions - List (filter: status, scope)

  • Query params: status, scope, valid_from, page, per_page
  • Response: Paginated list with minimal fields

• POST /api/v1/work-instructions - Create

  • Validate: title required, sections array, valid_from date
  • Generate instruction_number automatically
  • Set status to 'draft' by default

• GET /api/v1/work-instructions/{id} - Get details

  • Return full instruction with sections
  • Include acknowledgment stats if published

• PATCH /api/v1/work-instructions/{id} - Update

  • Only allow updates on 'draft' or 'review' status
  • Validation: same as create

• DELETE /api/v1/work-instructions/{id} - Soft delete

  • Only allow delete on 'draft' status
  • Permanently delete only if no acknowledgments exist

• POST /api/v1/work-instructions/{id}/publish - Publish

  • Change status to 'published'
  • Trigger notification job (Phase 3)
  • Increment version if editing published instruction

Templates

• GET /api/v1/work-instruction-templates - List templates

  • Filter by category
  • Include both system and tenant templates

• POST /api/v1/work-instruction-templates - Create template

  • Only non-system templates (tenant-specific)

Standard Blocks

• GET /api/v1/standard-blocks - Get all standard blocks
  • Return localized content based on Accept-Language header
  • Group by category

Acknowledgments

• POST /api/v1/work-instructions/{id}/acknowledge - Acknowledge

  • Record user, timestamp, IP, user agent
  • Validation: User must be in scope
  • Idempotent: Return existing if already acknowledged

• GET /api/v1/work-instructions/{id}/acknowledgments - Get acknowledgment list

  • Manager only: See who acknowledged
  • Paginated response

---

4. Authorization (Policies)

• WorkInstructionPolicy:
  • viewAny: All authenticated users
  • view: User in scope OR manager
  • create: Manager role only
  • update: Creator OR manager
  • delete: Creator only (if draft)
  • publish: Manager role only
  • viewAcknowledgments: Manager role only

---

5. Validation (Form Requests)

• StoreWorkInstructionRequest
• UpdateWorkInstructionRequest
• PublishWorkInstructionRequest
• AcknowledgeWorkInstructionRequest

---

6. Seeders (Sample Data)

• System templates seeder (3-5 common templates)
• Standard blocks seeder (German legal texts):
  • DSGVO Grundsatz
  • Meldepflichten
  • Brandschutz Grundregeln
  • Erste Hilfe Verpflichtungen

---

7. Tests (Pest)

Feature Tests:

• List work instructions (filter, pagination)
• Create work instruction (validation, auto-numbering)
• Update work instruction (only draft/review)
• Delete work instruction (only draft, no acknowledgments)
• Publish work instruction (status change)
• Acknowledge work instruction (idempotency, scope validation)
• Get templates (system + tenant)
• Get standard blocks (localized content)

Unit Tests:

• WorkInstruction model scopes
• Automatic instruction numbering
• Scope validation logic
• Policy authorization rules

Coverage Target: ≥80% for new code

---

✅ Acceptance Criteria

• All migrations created and tested (up + down)
• All 4 models implemented with relationships
• All API endpoints functional (11 routes)
• Authorization policies enforced on all routes
• Form request validation complete
• Seeders populate sample data correctly
• All tests passing (≥80% coverage)
• PHPStan level max passing
• Laravel Pint passing
• REUSE 3.3 compliant (SPDX headers)
• CHANGELOG.md updated (Added section)
• API documented in OpenAPI spec (SecPal/contracts)
• No breaking changes to existing endpoints
• Multi-language support (standard blocks in de + en)

---

🔗 Dependencies

• Depends on:
  • 🔐 Implement RBAC System (Role-Based Access Control) #5 (RBAC System) - for authorization
  • [EPIC] Multi-Language Support with Translation.io #83 (Multi-Language) - for i18n support
• Blocks:
  • 📋 Build Work Instruction Editor (Dienstanweisungs-Konfigurator) frontend#32 (Frontend Editor) - needs API to function
• Part of: Epic [EPIC] Work Instructions Management (Dienstanweisungen) #101 (Phase 1)

---

📝 Technical Notes

Instruction Numbering

// Auto-generate: DA-YYYY-NNN (Dienstanweisung-Year-Sequential)
public static function generateInstructionNumber(): string
{
    $year = now()->year;
    $latest = self::withTrashed()
        ->where('instruction_number', 'like', "DA-{$year}-%")
        ->orderBy('instruction_number', 'desc')
        ->first();
    
    $sequence = $latest 
        ? (int) substr($latest->instruction_number, -3) + 1 
        : 1;
    
    return sprintf('DA-%d-%03d', $year, $sequence);
}

Section Structure (JSON)

[
  {
    "id": "uuid",
    "type": "heading",
    "content": "1. Introduction",
    "order": 1,
    "locked": false
  },
  {
    "id": "uuid",
    "type": "paragraph",
    "content": "This instruction applies to...",
    "order": 2,
    "locked": false
  },
  {
    "id": "uuid",
    "type": "standard_block",
    "content": "standard_block_uuid",
    "order": 3,
    "locked": true
  }
]

---

🧪 Testing Strategy

// Example: Publish work instruction
test('manager can publish draft work instruction', function () {
    $manager = User::factory()->create();
    $manager->assignRole('manager');
    
    $instruction = WorkInstruction::factory()->create([
        'status' => 'draft',
        'created_by' => $manager->id,
    ]);
    
    $response = $this->actingAs($manager)
        ->postJson("/api/v1/work-instructions/{$instruction->id}/publish");
    
    $response->assertOk();
    expect($instruction->fresh()->status)->toBe('published');
});

test('guard cannot publish work instruction', function () {
    $guard = User::factory()->create();
    $guard->assignRole('guard');
    
    $instruction = WorkInstruction::factory()->create(['status' => 'draft']);
    
    $response = $this->actingAs($guard)
        ->postJson("/api/v1/work-instructions/{$instruction->id}/publish");
    
    $response->assertForbidden();
});

---

📝 PR Linking Instructions

When creating the PR for this sub-issue, use this in your PR description:

Fixes #<this-sub-issue-number>
Part of: #101

⚠️ Important:

• Do NOT use Fixes #101 - this is not the last sub-issue
• Ensure RBAC (🔐 Implement RBAC System (Role-Based Access Control) #5) and Multi-Language ([EPIC] Multi-Language Support with Translation.io #83) are complete before starting
• This PR will be large (~800-1000 LOC) - request large-pr-approved label if needed

---

Type: Sub-Issue (Backend)
Priority: High
Estimated Effort: 1-2 weeks
Sprint: 2-3 (Hybrid Approach - Backend Foundation)

[github-actions]

✅ This issue has been added to the SecPal Feature Roadmap with status: 💡 Ideas

New idea - needs discussion and evaluation

Status Flow:
💡 Ideas → 💬 Discussion → 📥 Backlog → 📋 Planned → 🚧 In Progress → 👀 In Review → ✅ Done

Note: Ideas that won't be implemented should be moved to 🚫 Won't Do with a reason.