Approval workflow with audit trail#

A multi-step approval flow: DRAFT → SUBMITTED → IN_PROGRESS → APPROVED (or REJECTED), with a field-level audit trail that records who changed what at each step.

Data model#

Three tables power the flow:

-- The entity being approved (e.g., an invoice)
ALTER TABLE invoice ADD COLUMN status VARCHAR(16) NOT NULL DEFAULT 'DRAFT';

-- Each step in the approval chain
CREATE TABLE workflow_step (
  id            BIGINT AUTO_INCREMENT PRIMARY KEY,
  invoice_id    BIGINT NOT NULL,
  step_order    INT    NOT NULL,          -- 1, 2, 3, ...
  group_name    VARCHAR(64),              -- "Finance", "Director", "Secretary"
  status        VARCHAR(16) NOT NULL DEFAULT 'PENDING',
  acted_by      VARCHAR(128),
  acted_at      TIMESTAMP,
  remarks       TEXT,
  CONSTRAINT fk_ws_invoice FOREIGN KEY (invoice_id) REFERENCES invoice(id)
);

-- Field-level change log per step
CREATE TABLE workflow_context (
  id            BIGINT AUTO_INCREMENT PRIMARY KEY,
  step_id       BIGINT NOT NULL,
  field_name    VARCHAR(64),
  old_value     VARCHAR(512),
  new_value     VARCHAR(512),
  CONSTRAINT fk_wc_step FOREIGN KEY (step_id) REFERENCES workflow_step(id)
);

Workflow service#

@Service
@RequiredArgsConstructor
public class InvoiceWorkflowService {

    private final WorkflowStepRepo   stepRepo;
    private final WorkflowContextRepo contextRepo;
    private final InvoiceRepo         invoiceRepo;

    @Transactional
    public void submit(Long invoiceId, String submittedBy) {
        var invoice = invoiceRepo.findById(invoiceId).orElseThrow();
        assertStatus(invoice, "DRAFT", "REJECTED");

        invoice.setStatus("SUBMITTED");
        invoiceRepo.save(invoice);

        stepRepo.save(WorkflowStepEntity.builder()
            .invoiceId(invoiceId)
            .stepOrder(nextStep(invoiceId))
            .groupName("Submitter")
            .status("COMPLETED")
            .actedBy(submittedBy)
            .actedAt(Instant.now())
            .build());
    }

    @Transactional
    public void approve(Long invoiceId, String approvedBy, Map<String, String> amendments) {
        var invoice = invoiceRepo.findById(invoiceId).orElseThrow();
        assertStatus(invoice, "SUBMITTED", "IN_PROGRESS");

        int order = nextStep(invoiceId);
        var step = stepRepo.save(WorkflowStepEntity.builder()
            .invoiceId(invoiceId)
            .stepOrder(order)
            .groupName(resolveGroup(approvedBy))
            .status("APPROVED")
            .actedBy(approvedBy)
            .actedAt(Instant.now())
            .build());

        // Audit every field the approver amended
        amendments.forEach((field, newValue) -> {
            String oldValue = getFieldValue(invoice, field);
            if (!Objects.equals(oldValue, newValue)) {
                contextRepo.save(WorkflowContextEntity.builder()
                    .stepId(step.getId())
                    .fieldName(field)
                    .oldValue(oldValue)
                    .newValue(newValue)
                    .build());
                setFieldValue(invoice, field, newValue);
            }
        });

        invoice.setStatus(isLastApprover(order) ? "APPROVED" : "IN_PROGRESS");
        invoiceRepo.save(invoice);
    }

    @Transactional
    public void reject(Long invoiceId, String rejectedBy, String remarks) {
        var invoice = invoiceRepo.findById(invoiceId).orElseThrow();
        assertStatus(invoice, "SUBMITTED", "IN_PROGRESS");

        invoice.setStatus("REJECTED");
        invoiceRepo.save(invoice);

        stepRepo.save(WorkflowStepEntity.builder()
            .invoiceId(invoiceId)
            .stepOrder(nextStep(invoiceId))
            .groupName(resolveGroup(rejectedBy))
            .status("REJECTED")
            .actedBy(rejectedBy)
            .actedAt(Instant.now())
            .remarks(remarks)
            .build());
    }

    private void assertStatus(InvoiceEntity invoice, String... allowed) {
        if (!Set.of(allowed).contains(invoice.getStatus())) {
            throw new BusinessException("Invoice is in status " + invoice.getStatus()
                + "; expected one of " + Arrays.toString(allowed));
        }
    }

    private int nextStep(Long invoiceId) {
        return stepRepo.countByInvoiceId(invoiceId) + 1;
    }
}

Wiring to a Palmyra handler#

The handler gates the status transitions; the workflow service owns the logic.

@Component
@CrudMapping(mapping = "/invoice", type = InvoiceModel.class,
             secondaryMapping = "/invoice/{id}")
public class InvoiceHandler extends AbstractHandler
        implements QueryHandler, ReadHandler, SaveHandler {

    @Override
    public Tuple preUpdate(Tuple tuple, Tuple dbTuple, HandlerContext ctx) {
        String status = (String) dbTuple.get("status");
        if (!Set.of("DRAFT", "REJECTED").contains(status)) {
            throw new BusinessException("Cannot edit invoice in status: " + status);
        }
        return tuple;
    }
}

Approve / reject actions go through a custom controller that calls the workflow service:

@PostMapping("/invoice/{id}/approve")
@PreAuthorize("hasPermission(#id, 'Invoice', 'INVOICE_APPROVE')")
public ResponseEntity<?> approve(@PathVariable Long id,
                                  @RequestBody Map<String, String> amendments) {
    workflowService.approve(id, auth.getUser(), amendments);
    return ResponseEntity.noContent().build();
}

Querying the audit trail#

Expose the trail through a simple read handler — or a NativeQueryHandler if you want the timeline flattened:

@Component
@CrudMapping(mapping = "/invoice/{invoiceId}/workflow", type = WorkflowStepModel.class)
public class WorkflowStepHandler implements QueryHandler {
    @Override
    public QueryFilter applyQueryFilter(QueryFilter filter, HandlerContext ctx) {
        filter.addOrderAsc("stepOrder");
        return filter;
    }
}