Music Store Management Platform

Domain Design: Delivery & Chain of Custody



# 1. Overview

The Delivery domain tracks the physical movement of instruments between the store and customers or institutions. It covers all four handoff types — store picking up from customer, store delivering to customer, customer dropping off at store, and customer picking up from store. Every instrument movement is recorded with condition, timestamp, responsible party, and recipient signature.



This domain is primarily used by the batch repair workflow for school instrument pickups and deliveries, but applies to any scenario where the store transports instruments.



# 2. Event Types

Event Type

Description

store_pickup

Store driver goes to customer/school location and collects instruments

store_delivery

Store driver brings repaired instruments to customer/school location

customer_dropoff

Customer brings instrument(s) into the store

customer_pickup

Customer comes to store to collect repaired instrument(s)



# 3. Database Schema

## 3.1 delivery_event

Column

Type

Notes

id

uuid PK



company_id

uuid FK

Tenant scoping

location_id

uuid FK

Physical location originating or receiving the delivery

event_number

varchar

Human-readable ID e.g. DEL-2024-0018

event_type

enum

store_pickup|store_delivery|customer_dropoff|customer_pickup

repair_batch_id

uuid FK

Nullable — links to batch if applicable

account_id

uuid FK

Customer/school account

status

enum

scheduled|in_transit|completed|cancelled

scheduled_date

date

Planned date

scheduled_time_window

varchar

e.g. '8am-10am' — for school scheduling

actual_datetime

timestamptz

When event actually occurred

address

jsonb

Location — may differ from account address

driver_employee_id

uuid FK

Store employee performing pickup/delivery

recipient_name

varchar

Name of person who received/released instruments

recipient_signature

text

Base64 signature capture from iOS app

recipient_signed_at

timestamptz

When signature was captured

instrument_count_expected

integer

How many instruments expected in this event

instrument_count_actual

integer

How many actually transferred — discrepancy flagged

notes

text

Driver/staff notes

created_at

timestamptz



updated_at

timestamptz





## 3.2 delivery_event_item

Records each individual instrument included in a delivery event with condition at time of transfer.

Column

Type

Notes

id

uuid PK



delivery_event_id

uuid FK



repair_ticket_id

uuid FK

Which repair ticket this instrument belongs to

inventory_unit_id

uuid FK

Nullable — if instrument is in system

instrument_description

varchar

Free text for unrecognized instruments

serial_number

varchar

If known

condition

enum

excellent|good|fair|poor|damaged

condition_notes

text

Detailed condition description at this handoff

photo_urls

text[]

S3 URLs of condition photos taken at handoff

was_transferred

boolean

False if instrument was on manifest but not present

missing_notes

text

Explanation if was_transferred = false

created_at

timestamptz





# 4. Chain of Custody

The full custody history of any instrument can be reconstructed by querying delivery_event_item records for a given repair_ticket_id or inventory_unit_id, ordered by created_at. This provides a complete timeline:



Example custody chain for a school instrument:1. store_pickup      → 2024-09-05 08:30  Driver: J. Smith   Condition: good   Received from: Lincoln Middle School, signed by: M. Johnson2. [repair work at store]3. store_delivery    → 2024-09-12 09:15  Driver: J. Smith   Condition: excellent   Delivered to: Lincoln Middle School, signed by: M. Johnson



# 5. Business Rules

- Every instrument transfer must have a delivery_event_item record — no undocumented transfers

- Condition documented at every handoff — incoming and outgoing

- Photo capture recommended at pickup — particularly for school batch repairs

- Signature capture required for store_pickup and store_delivery events

- Instrument count discrepancy between expected and actual requires staff notes before completing event

- Missing instrument (was_transferred = false) triggers alert to store manager

- Delivery event completion automatically updates linked repair_ticket status to 'delivered'

- Batch delivery event completion triggers batch status update to 'delivered'

- Repair ticket cannot be invoiced until delivery_event_item shows was_transferred = true



# 6. iOS App Integration

The delivery workflow is a primary use case for the iOS mobile app. The driver uses the app at the school location to:

- Pull up the scheduled delivery event

- Check off each instrument as it is received or handed over

- Record condition and take photos for each instrument

- Capture recipient signature on screen

- Complete the event — syncs to backend immediately or queues for sync if offline



Offline support is important here — schools may have poor cellular connectivity. The app queues all event data locally and syncs when the driver returns to a connected area.



# 7. Reporting

- Delivery schedule — upcoming pickups and deliveries by date

- Driver activity log — events completed per employee

- Outstanding deliveries — instruments at store awaiting return to customer

- Instrument location report — where is each instrument right now

- Condition change report — instruments that arrived in worse condition than sent

- Signature audit — all signed receipts by account and date