Done/Reopen (Done→Waste) — Official Spec (Offline-safe, Two-way, No ClickHouse Queries) — Scale-based

Summary

This spec defines Done and Reopen for a package tag with:

  • Two-way control: initiated from device or mvpapp UI
  • Offline-first device UX: Pending / Confirmed without disrupting operators
  • Accounting correctness: Done creates a real Waste event with a concrete weight
  • Reopen required: closed tags cannot receive additional inflow until reopened
  • Idempotent + reorder-safe: retries/duplicates/offline replays never double-waste
  • No mvpapp queries to ClickHouse or inventory-service
  • Waste attribution: “X lbs of waste came from tag …123”
  • Scale-based identity: command origin is identified by scale_id

The system achieves this by maintaining Postgres projection tables via triggers so Done can read remaining weight quickly without heavy aggregation at request time.

Components in scope

  • Device
  • mvpapp (Laravel) + its Postgres DB (authoritative executor and outbox source)

(Not in scope for changes: MQTT broker, outbox relay, ClickHouse, inventory-service.)

MQTT topics

Topic prefix

Config/env var:

  • MQTT_INVENTORY_TOPIC_PREFIX
  • suggested default: BayKinetic/inventory/customer

Topic templates

  • Commands: {MQTT_INVENTORY_TOPIC_PREFIX}/{client}/tags/commands
  • State updates (required): {MQTT_INVENTORY_TOPIC_PREFIX}/{client}/tags/state_updates
  • Command results (optional): {MQTT_INVENTORY_TOPIC_PREFIX}/{client}/tags/command_results/{scale_id}

Message contracts

1) Command message (device or mvpapp publishes)

Topic: {...}/{client}/tags/commands

{
  "event_type": "package_tag_command",
  "event_id": "uuid",
  "type": "done_to_waste",
  "origin": "device|mvpapp",

  "client": "ACME",
  "facility_id": 10,
  "package_tag": "...123",

  "scale_id": "c_c_p_0003_2025_2434",
  "occurred_at": "2025-12-19T18:01:02Z",
  "note": "optional"
}
  • type: done_to_waste | reopen
  • event_id: required idempotency key
  • scale_id: required origin scale identifier

2) State update message (mvpapp publishes; sync channel)

Topic: {...}/{client}/tags/state_updates

{
  "event_type": "package_tag_state_update",
  "client": "ACME",
  "facility_id": 10,
  "package_tag": "...123",

  "is_closed": true,
  "updated_at": "2025-12-19T18:02:10Z",

  "last_event_id": "uuid",
  "last_action": "done_to_waste",
  "result": "applied|ignored",
  "origin": "device|mvpapp"
}

3) Optional per-scale command result (mvpapp publishes)

Topic: {...}/{client}/tags/command_results/{scale_id}

{
  "event_id": "uuid",
  "status": "applied|ignored|rejected|error",
  "message": "optional",
  "applied_at": "2025-12-19T18:02:10Z"
}

Device requirements

Offline-first durability (required)

Device persists each Done/Reopen action as a durable event in the device’s local Postgres Outbox, then publishes asynchronously when connected.

  • Device must not treat MQTT publish success as “applied”.
  • Confirmation is derived from state_updates (and optionally command_results/{scale_id}).

UI behavior (required)

For each tag, device maintains:

  • logical state: Open | Closed
  • sync state: Pending | Confirmed

Rules:

  • On Done press: set Closed (Pending) immediately.
  • On Reopen press: set Open (Pending) immediately.
  • On state_updates: update local cached tag state; server state wins; mark Confirmed.

Local enforcement (required)

If tag is Closed (Pending or Confirmed), device must block weighing into that tag (prevent generating records that would result in processing_weight_records.new_package_tag = tag) until reopened.

MQTT behavior (required)

  • Publish queued commands with QoS1 to {...}/{client}/tags/commands.
  • Subscribe to {...}/{client}/tags/state_updates.
  • Optionally subscribe to {...}/{client}/tags/command_results/{scale_id}.

mvpapp requirements (authoritative executor)

Two-way initiation (required)

  • Device-originated commands: mvpapp subscribes to {...}/{client}/tags/commands.
  • mvpapp UI-originated commands: mvpapp publishes the same command payload to the same topic.

Idempotency (required)

mvpapp must store event_id with a uniqueness guarantee. Duplicate/replayed commands must not create duplicate Waste events.

Authoritative state machine (required)

Let current state be Open or Closed:

  • Open + done_to_waste
  • Read remaining_lbs(tag) from Postgres projection tag_balances
  • Insert one Waste event into processing_weight_records (see below)
  • Mark tag Closed

  • Emit state_updates with result=applied

  • Closed + done_to_waste

  • No-op (do not waste again)

  • Emit state_updates with result=ignored

  • Closed + reopen

  • Mark tag Open

  • Emit state_updates with result=applied

  • Open + reopen

  • No-op
  • Emit state_updates with result=ignored

Transactionality (required)

For done_to_waste, processing must be atomic:

  • record idempotency + audit
  • state transition
  • waste event insert (if remaining > 0)

No ClickHouse queries (required)

mvpapp must not query ClickHouse or inventory-service. Remaining is sourced from Postgres projections only.

Publishing (required)

  • Always publish {...}/{client}/tags/state_updates.
  • Optionally publish {...}/{client}/tags/command_results/{scale_id}.

Postgres schema requirements (mvpapp-owned DB)

1) Tag state + idempotency (required)

package_tag_state_events (audit log; idempotency)

Must include scale_id for audit/troubleshooting.

CREATE TABLE package_tag_state_events (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  mqtt_event_id uuid NOT NULL,
  client varchar(255) NOT NULL,
  facility_id bigint NOT NULL,
  package_tag varchar(255) NOT NULL,
  action text NOT NULL CHECK (action IN ('done_to_waste', 'reopen')),
  scale_id varchar(255) NULL,
  note text NULL,
  occurred_at timestamptz NOT NULL,
  created_at timestamptz NOT NULL DEFAULT now(),
  UNIQUE (mqtt_event_id)
);

package_tag_state (current state)

CREATE TABLE package_tag_state (
  client varchar(255) NOT NULL,
  facility_id bigint NOT NULL,
  package_tag varchar(255) NOT NULL,
  is_closed boolean NOT NULL DEFAULT false,
  updated_at timestamptz NOT NULL DEFAULT now(),
  last_mqtt_event_id uuid NULL,
  last_action text NULL,
  PRIMARY KEY (client, facility_id, package_tag)
);

2) Projection tables for remaining (required)

Because the probe writes:

  • weight_records
  • processing_weight_records with empty source_package_tag
  • processing_weight_record_package_tags as the true source mapping

…Postgres must maintain projections:

pwr_effective_source

Maps: processing_weight_record_id -> effective_source_tag

tag_balances

Key: (client, facility_id, package_tag) Maintains:

  • inflow totals (lbs)
  • outflow totals (lbs)
  • remaining_lbs (stored or computed)

3) Trigger-based maintenance (required)

Postgres triggers maintain projections incrementally:

  • weight_records: inflow to package_tag
  • processing_weight_records: inflow to new_package_tag
  • processing_weight_record_package_tags:
  • upsert pwr_effective_source
  • apply outflow to tag_balances for the mapped source tag based on linked processing_weight_records.weight_value (convert uom → lbs)
  • handle mapping changes by reversing old outflow and applying new outflow

4) Guard trigger (“reopen required”) (required)

On processing_weight_records BEFORE INSERT OR UPDATE:

  • if NEW.new_package_tag references a tag where package_tag_state.is_closed=true, reject the write.

Waste event definition (Done behavior)

When mvpapp applies Done for tag T:

  • read remaining_lbs from tag_balances for T
  • insert one Waste row into processing_weight_records with:
  • item_type = 'Waste'
  • weight_value = remaining_lbs
  • unit_of_measure = 'lb' (or normalized)
  • new_package_tag = NULL
  • source_package_tag = T (set directly for system-generated waste)
  • optional void_reason = 'done_to_waste'
  • set scale_id on the row if you want to attribute the action to the initiating scale

This provides an immutable Waste event and preserves attribution to the source tag.

Reporting requirement: “Waste by source tag”

Waste attribution is satisfied because Waste events carry source_package_tag. Reporting can group Waste rows by that field, optionally filtering void_reason='done_to_waste'.

Where Postgres triggers live (implementation ownership)

All new tables/functions/triggers belong in mvpapp’s Postgres schema and must be deployed via mvpapp migrations. They do not belong in inventory-service.

Acceptance criteria

  • Done/Reopen can be initiated from device or mvpapp UI and converge via state_updates.
  • Device works offline: Pending immediately; Confirmed upon observing state_updates.
  • Closed tags cannot receive new inflow until reopened (device enforcement + DB guard trigger).
  • Done produces exactly one Waste event per Open→Closed transition, never duplicated under retries/offline replay.
  • mvpapp does not query ClickHouse/inventory-service; Done uses tag_balances.remaining_lbs.