Package Tag Done/Reopen — mvpapp (Laravel) implementation notes

This page describes how mvpapp should implement the platform contract:

Responsibilities (authoritative executor)

Subscribe: commands

  • Topic: {MQTT_INVENTORY_TOPIC_PREFIX}/{client}/tags/commands
  • Consume both device-originated and UI-originated commands.
  • Command origin identity is scale-based (scale_id).

Idempotency + state machine (authoritative)

Implement a state machine with unique event_id enforcement:

  • Open + done_to_waste:
  • Read remaining_lbs from tag_balances
  • Write a Waste event into processing_weight_records for that weight
  • Transition tag to Closed

  • Publish state_updates with result=applied

  • Closed + done_to_waste:

  • No-op (do not waste again)

  • Publish state_updates with result=ignored

  • Closed + reopen:

  • Transition tag to Open

  • Publish state_updates with result=applied

  • Open + reopen:

  • No-op
  • Publish state_updates with result=ignored

Critical constraint

  • mvpapp must not query ClickHouse / inventory-service for Done.
  • Done must use Postgres projection: tag_balances.remaining_lbs.

Publish: state updates (required)

  • Topic: {MQTT_INVENTORY_TOPIC_PREFIX}/{client}/tags/state_updates
  • Publish for both applied and ignored outcomes (sync channel for device UX).

Optional:

  • Topic: {MQTT_INVENTORY_TOPIC_PREFIX}/{client}/tags/command_results/{scale_id}

Data correctness expectations

  • Exactly-once waste per Open→Closed transition:
  • duplicates/replays must not double-write waste
  • Closed blocks inflow:
  • DB-side guard trigger should prevent new inflow to closed tags (and device should also block locally)
  • Waste attribution:
  • Ensure the waste event can be attributed back to the closed tag for reporting (“X lbs came from tag …123”)

Postgres ownership (mvpapp DB)

All new tables/functions/triggers for this feature belong in mvpapp’s Postgres schema and ship via mvpapp migrations (not inventory-service).

Minimum schema expectations (see canonical spec for details):

  • package_tag_state_events (unique mqtt_event_id, includes scale_id for audit)
  • package_tag_state (current open/closed state)
  • projection tables:
  • tag_balances (must provide remaining_lbs)
  • pwr_effective_source (effective source tag mapping)
  • triggers to maintain projections from:
  • weight_records
  • processing_weight_records
  • processing_weight_record_package_tags
  • guard trigger: reject processing_weight_records that attempt to set new_package_tag to a closed tag

Waste row requirements (Done)

When applying done_to_waste, the inserted Waste row in processing_weight_records must:

  • set weight_value = remaining_lbs and normalize unit_of_measure (e.g. 'lb')
  • set new_package_tag = NULL
  • set source_package_tag = <closed_tag> (system-generated waste should set this directly)
  • optionally set void_reason = 'done_to_waste'
  • optionally set scale_id to the initiating scale for audit

Suggested test checklist

  • Device publishes done_to_waste while offline, then reconnects and replays:
  • waste written once
  • state ends closed
  • state_updates published deterministically for replays (ignored or same final state)
  • UI publishes reopen:
  • state opens
  • state_updates published
  • Attempt inflow while closed:
  • rejected by DB enforcement