Command Repository

The Command Repository is the relational store behind the Command Router. It is the authoritative history of what was asked of a device, when, how many times we tried, and how it ended. Every status the API reports comes from here.

Storage profile

PostgreSQL, single logical database. Commands and their attempts are retained for 90 days for audit and debugging, then archived out of the hot path.

Data model

The schema is built around the command and its lifecycle: the command itself, each delivery attempt, the terminal result, and the (potentially large) payload split into its own table. The ERD is embedded on the Overview tab.

Notes

COMMAND.idempotency_key is unique, which is what makes the API’s idempotencyKey guarantee enforceable at the storage layer.
DELIVERY_ATTEMPT rows are append-only — one per try by the Delivery Engine — giving a full retry timeline per command.
COMMAND_PAYLOAD is separated from COMMAND so that listing and status queries stay fast and never drag large bodies through the index scans.

Command Repository

Storage profile

PostgreSQL, single logical database. Commands and their attempts are retained for 90 days for audit and debugging, then archived out of the hot path.

Data model

Notes

COMMAND.idempotency_key is unique, which is what makes the API’s idempotencyKey guarantee enforceable at the storage layer.
DELIVERY_ATTEMPT rows are append-only — one per try by the Delivery Engine — giving a full retry timeline per command.
COMMAND_PAYLOAD is separated from COMMAND so that listing and status queries stay fast and never drag large bodies through the index scans.