# ADR-120 Append-Only Shipment Lifecycle Event Store + Delete-Protected Final Record

## Status

Proposed, 2026-07-27.

## Status History

```yaml
status_history:
  - date: 2026-07-27
    status: Proposed
    changed_by: hkl
    reason: Initial proposal — lifecycle tracking + retention/deletion model for DispatchWithAutomation.
    changed_via: adr-kit (360lm) — authored from DL CLI via SSH
```

## Context

Performance analysis needs the full lifecycle of every shipment: the booking-time EDD (the
*commitment*) plus every subsequent status and EDD change through actual delivery, retained for the
campaign's life. Two hard realities (Fable review, DL 2026-07-27): couriers **backfill and reorder**
events (an event observed at 6pm may be dated 11am, sometimes earlier than an already-recorded one),
and an AWB can **legitimately go backwards** (assigned→unassigned→new). Separately, the owner wants
to purge transactional history after a campaign closes to keep the DB lean — but the final record
(booking details incl. cost, final delivery date) must be **permanent**, and a CSV export must be
possible **before** any purge. The existing module already has an append-only `dispatch.tracking_events`
to extend.

## Decision

Model lifecycle state in three layers under schema `dispatch`:
1. **`shipment_events`** — append-only (extends `tracking_events`): `(shipment_id, event_time,
   observed_at, status_normalized, status_raw, edd, source, raw_payload jsonb)`, one row per
   *observed change* (proxy diffs vs last-known before inserting). Store **both** `observed_at` and
   courier `event_time`; define "EDD changed" as a change between consecutive *observations*.
2. **`shipment_current`** — derived view (or trigger-maintained table): latest status + latest EDD +
   immutable `booked_edd` + delta. **Amended 2026-07-27 (see ADR-118's both-channels amendment):**
   also exposes `delivered_confirmed_by_courier` + a `display_status` that reads "Delivered
   (Unconfirmed)" until a source other than `'shiprocket'` independently confirms delivery — the
   append-only log here retains every source's observations, which is exactly what makes that
   cross-check possible without a schema change.
3. **`shipment_final`** — a **physically separate, DELETE-protected** table (REVOKE DELETE from
   web_anon + a `BEFORE DELETE` trigger that raises): booking details, cost, courier, AWB, booked
   EDD, final delivery date, final status; written by trigger/RPC when a terminal status lands.

Retention purge is an **Admin-only RPC, permitted only after a campaign is marked `closed`**, and it
deletes from `shipment_events` (and other transactional tables) **only** — never `shipment_final`.
The PWA **must offer a CSV download of the data before the purge executes** (mandatory pre-delete
step). AWB is modeled as a changeable shipment attribute, never part of shipment identity.

**Decision Maker:** hkl

## Implementation Notes

- **DDL:** extend `migrate_dispatch_v*.sql` (dev-first, ADR-015 / DB_MIGRATIONS lock). `shipment_final` gets `REVOKE DELETE` + `BEFORE DELETE` guard trigger (do NOT rely on "skip the last event row").
- **Purge RPC:** `dispatch.purge_campaign_transactional(campaign_id)` — checks `status='closed'` + Admin role; the PWA calls the CSV-export endpoint first and only enables purge after a successful download.
- **Diff-on-ingest:** `shiprocket-proxy` inserts an event only when `status_normalized` or `edd` differs from last-known for that shipment.
- **Find sites:** `grep -rn "shipment_final\|purge_campaign" /var/www/360lm/dispatch /opt/shiprocket-proxy`

## Alternatives Considered

- **"Final record = the last event row we don't delete."** Rejected (Fable): one bad WHERE clause erases the commitment; a physically separate delete-protected table is the safe design.
- **Key the timeline on `observed_at` only (or `event_time` only).** Rejected: observed-only corrupts SLA/EDD-slip math when couriers backfill; event-only loses "what did we know when." Store both.
- **Never delete (archive everything).** Rejected by owner for DB leanness; but purge is gated (closed + Admin + CSV-first) so it's safe.

## Consequences

**Positive:**
- Full commitment→actual history for analytics; immutable final record survives any purge; backfill/reorder and AWB-revert handled correctly; CSV safety net before deletion.

**Negative / Trade-offs:**
- Two-timestamp model + separate final table adds schema complexity; purge is deliberately friction-ful (closed + Admin + export).

**Risks and mitigations:**
- *Accidental history loss* → purge gated on campaign-closed + Admin role + mandatory CSV export.
- *Event dedup misses a real change* → diff on `(status_normalized, edd)`, keep `raw_payload` for re-derivation.

## Related Decisions

- ADR-118 (feeds events), ADR-117 (proxy writes normalized), ADR-041 (supersede-not-delete lineage), ADR-045 (edit/delete governance), ADR-123 (reports read these), ADR-070 (IST timestamps).

## References

- `/var/www/360lm/docs/MDD_dispatch_automation.md` §6, §14.
- Fable review (event-model pitfalls), DL 2026-07-27.