# Data model and service contracts

> Historical design requirements: consult [chapter 30](30-current-architecture.md) for the current implementation and outstanding work.

Current additions: migrations 014/015 implement tenant-scoped `DeviceLocationHistory` with reported/manual source, label and actor. The service exposes bounded location history and a Device Write manual-location command. Current SQL also contains the model catalog, command/deployment/application tables, IoT certificate lifecycle, user/role/audit records and current-save parameter workspace described in later chapters. Proposed revision/publish aggregates below remain design material unless chapter 30 says they are implemented.

## Logical relational model

This is a proposed logical schema, not production DDL. SQL Server is the working database choice. All timestamps are UTC; display timezone belongs to user/customer preferences. Immutable IDs are independent of serial numbers, bank TIDs and merchant IDs.

| Aggregate / tables | Important keys and fields | Invariants |
| --- | --- | --- |
| Customer | CustomerId, Code, Name, Status, TimeZone, RowVersion | Unique customer code; active customer has an active admin |
| User, CustomerMembership, RoleGrant | UserId, CustomerId, Role, Status, AuthzVersion | Unique user/customer membership; platform role separate |
| DeviceInventory | DeviceId, Manufacturer, SerialNumber, Model, Platform, Lifecycle | Platform-owned; proposed unique manufacturer + serial |
| DeviceAssignment, AssignmentHistory | DeviceId, CustomerId, AssignmentEpoch, StartUtc, EndUtc, RowVersion | At most one current assignment per device |
| DeviceCredential | CredentialId, DeviceId, Epoch, Fingerprint, Status, ExpiresUtc | No plaintext private key; revoked credentials cannot connect |
| Merchant, Site, DevicePlacement | CustomerId, MerchantId, SiteId, DeviceId, dates | Same customer; placement is not device ownership |
| DeviceGroup | CustomerId, GroupId, ParentGroupId, Name, RowVersion | No cycles; same-customer parent; proposed depth limit 8 |
| DeviceGroupMember | CustomerId, DeviceId, GroupId | One primary group per assigned device |
| Tag, DeviceTag | CustomerId, TagId, NormalizedName, DeviceId | Unique normalized tag per customer; many tags per device |
| DeviceState | CustomerId, DeviceId, Epoch, LastReceivedUtc, ReportedUtc, Sequence, Payload | Current snapshot, one per assignment; capabilities and freshness |
| TelemetryEvent | CustomerId, DeviceId, Epoch, EventId, ReceivedUtc, Type | Time-partitionable; deduplicate by device/epoch/event |
| DeviceApplication | CustomerId, DeviceId, PackageId, Version, LastSeenUtc | Observed inventory, not desired software |
| Application, ApplicationVersion | CustomerId, ApplicationId, VersionId, Platform, Manifest, Hash, ObjectKey, ReleaseState | Immutable version; approved artifact only |
| ParameterSchema, SchemaVersion | CustomerId, SchemaId, ApplicationId, VersionId, DefinitionJson, State, Hash | Published version immutable |
| CatalogDefinition, CatalogRecord | CustomerId, SchemaVersionId, CatalogId, RecordId, ValuesJson | Version-scoped typed references |
| ParameterTemplate, TemplateVersion | CustomerId, ApplicationId, SchemaVersionId, ValuesJson | Copy/apply input; never implicit live inheritance |
| DeviceConfiguration, ConfigurationRevision | CustomerId, DeviceId, ApplicationId, SchemaVersionId, RevisionId, Json, Hash | One current draft and published pointer per device/app; immutable revisions |
| Deployment, DeploymentTarget | CustomerId, DeploymentId, DeviceId, ArtifactOrRevisionId, Epoch, State | Frozen target set and payload; one per target |
| DeviceCommand, CommandAttempt | CustomerId, CommandId, DeviceId, Epoch, Type, ExpiresUtc, State | Idempotency key and per-attempt outcome |
| RemoteSession | CustomerId, SessionId, DeviceId, Epoch, ActorId, Mode, ExpiresUtc | One controlling operator per device by default |
| Outbox, Inbox, IdempotencyRecord | CustomerId, MessageId, DedupKey, Hash, LeaseUntil, Attempts | Atomic write/outbox; transactional consume/dedup |
| AuditEvent | CustomerId optional, ActorId, Action, ResourceId, Outcome, Reason, CorrelationId | Append-only app access; sensitive values redacted |

Use composite unique keys such as `(CustomerId, DeviceId, AssignmentEpoch)` as referenced ownership keys for operations and history. Global inventory remains platform-owned; the current assignment projection provides customer-scoped joins. A composite FK on `(CustomerId, GroupId)` prevents foreign groups. Parent traversal and cycle validation run in a transaction with locking/concurrency protection.

## Indexes and concurrency

Start with customer-first indexes on device status/last-seen, group and tag lookups, command state/due date, application identity/version, and audit timestamp. Use keyset pagination for growing telemetry/audit lists, and bounded paging for device grids. Store latest device state separately from append-only event history. Review actual execution plans before adding speculative indexes.

Use SQL rowversion or equivalent expected-version tokens for device assignment, group editing, drafts and approvals. A conflict returns current version information without overwriting another operator's changes. Worker claims use transactional leases with an owner, expiry and fencing token; expired leases may be reclaimed. An old lease cannot mark a newer claim complete.

## Service catalogue

Version protobuf packages as `uic.tms.v1`. Keep transport DTOs separate from SQL entities. The following methods are contract proposals, not generated API code.

| gRPC service | Representative methods | Authorization |
| --- | --- | --- |
| CustomerService | CreateCustomer, SuspendCustomer, ListCustomers | SuperAdmin for lifecycle |
| MembershipService | InviteAdmin, GrantRole, RevokeMembership | Scoped admin; constrained delegation |
| DeviceInventoryService | RegisterDevice, AssignDevice, StartTransfer | SuperAdmin |
| DeviceService | ListDevices, GetDevice, UpdateProperties, SetPlacement | Customer-scoped permissions |
| OrganizationService | CreateGroup, MoveGroup, SetDeviceGroup, SetTags | Customer fleet administration |
| SchemaService | SaveDraft, ValidateDraft, PreviewMigration, PublishVersion | Schema edit/publish permissions |
| ConfigurationService | GetDraft, SaveDraft, ResolveTemplate, PublishRevision | Parameter permissions |
| ApplicationService | InitiateUpload, CompleteUpload, ValidateArtifact, ReleaseVersion | Upload/release permissions; validator service role |
| OperationService | PreviewTargets, CreateDeployment, QueueCommand, CancelPending, GetProgress | Operation-specific permission |
| DeviceIngestService | RecordTelemetryBatch, RecordInventory, AcknowledgeCommand | Device gateway identity + verified enrollment |
| DispatchService | LeaseWork, RenewLease, RecordDispatchAttempt | Dispatcher role only |
| RemoteSessionService | AuthorizeSession, RedeemTicket, EndSession | Remote permission + relay identity |
| AuditService | SearchAudit, RequestExport | Scoped audit access |

## Contract conventions

Each customer RPC has a CustomerId selector and authenticated context; each mutation has RequestId/IdempotencyKey, optional ExpectedVersion and CorrelationId. Device-bound work includes AssignmentEpoch. DataService verifies all selectors against identity and current ownership. Use explicit enums with Unspecified = 0; prohibit unspecified values for required actions. Preserve protobuf field numbers and reserve removed fields.

Idempotency uniqueness is `(CustomerId, ActorOrServiceId, RpcMethod, IdempotencyKey)`. Store a canonical request hash and durable result; the same key with a different payload returns a conflict. Retain keys at least as long as the supported retry window, proposed seven days for operator mutations and the command lifetime plus seven days for delivery results.

Return stable error codes and safe field-level validation details. Proposed mappings: malformed input → InvalidArgument; missing/expired identity → Unauthenticated; insufficient capability → PermissionDenied; inaccessible customer resource → NotFound; optimistic conflict → Aborted; unmet lifecycle condition → FailedPrecondition; throttling → ResourceExhausted; dependency failure → Unavailable. No SQL exception text reaches the browser.

Set proposed deadlines of 5 seconds for reads and 15 seconds for ordinary mutations; expensive scans, exports and deployments are asynchronous jobs. Propagate cancellation. Retry reads and idempotent mutations only, with bounded exponential backoff and jitter. A timeout is an unknown outcome until the original idempotency key or operation ID is queried.

## Example command request

```json
{
  "customerId": "bank-example",
  "deviceId": "device-example",
  "assignmentEpoch": 4,
  "commandType": "REBOOT",
  "idempotencyKey": "example-request-001",
  "reason": "Scheduled maintenance",
  "expiresAtUtc": "2026-09-24T02:15:00Z"
}
```

All identifiers are illustrative. Actor identity comes from a verified delegation token, not this JSON. The response returns CommandId and Queued/PendingApproval state, never a fabricated device success.

## Dapper transaction example

```sql
-- Illustrative SQL shape; final table names and DDL are pending.
UPDATE DeviceConfiguration
SET DraftJson = @Json
WHERE CustomerId = @CustomerId
  AND DeviceId = @DeviceId
  AND ApplicationId = @ApplicationId
  AND RowVersion = @ExpectedVersion;
-- Require one affected row. In the same transaction, append audit.
-- Publishing additionally inserts an immutable revision and outbox event.
```

Use a service-owned SQL connection and explicit transaction passed to every related Dapper call. No dynamic schema/table names or SQL fragments may come from user input. Backup restore, migration rollback strategy and compatibility tests are deployment responsibilities.

## Implemented model catalog

The local implementation now includes a global DeviceModel catalog with ModelId, Brand, Model and OS (Linux or Android). Brand + Model + OS is unique, with case-insensitive brand/model matching. SuperAdmins maintain the catalog; authenticated bank admins may read it. Every registered device references a required ModelId. Fleet displays derive brand, model and OS from that catalog, so corrections apply consistently to linked devices.

OperatorApi exposes ListModels and SaveModel. CreateDevice now requires model_id; its original free-text model/platform request fields are deprecated. Migration 002 backfills legacy model/OS pairs under brand “Unspecified”, preserving existing device identities and assignments. See the development guide for operational details.