Field operations

Objectives, tours and their history, visits with GPS check-in, visit types and checklists, photos, reports and location pings.

Delegates' objectives, tours (tournées), visits and positions (cahier §4.5, §6.1–§6.4, §6.8, §6.9 — FLD). Prisma file: field.prisma.

The delegate app is offline-first (SYN-04), which shapes these tables:

Device time and server time. Every delegate action carries occurredAt (device); the milestone columns (startedAt, checkedInAt, endedAt…) hold device times, and createdAt of the append-only rows is the server receipt time (receivedAt). Implausible device times are refused (422 OCCURRED_AT_OUT_OF_RANGE).
Client ids. Ad-hoc visits, visit photos and visit reports may be created with a client-generated UUIDv7 and are upserted idempotently (offline sync design).
Optimistic locking. Tour.version and Visit.version turn a stale offline edit into a sync CONFLICT instead of a silent overwrite.
Positions are sampled only while a tour is IN_PROGRESS and the delegate has given consent (FLD-15, SEC-07); they are kept 90 days (proposal).

Default rules (settings): GPS tolerance 150 m with accuracy ≤ 100 m, sampling every 5 min or 200 m, unlock window 24 h, reopening within 7 days.

Objectives

Objective

A target for one delegate, one metric and one period (FLD-02): visits, sign-ups, installs, first orders, orders, revenue. Table objectives · written by field (admin.objectives.crud, BO-20).

Field	Prisma type	Null	Default	Constraints / notes
`id`	`String @db.Uuid`	—	`uuid(7)`	PK.
`delegateId`	`String @db.Uuid`	—	—	FK → DelegateProfile · `Restrict`.
`metric`	`ObjectiveMetric`	—	—	—
`period`	`ObjectivePeriod`	—	—	—
`periodStart`	`DateTime @db.Date`	—	—	First day (Africa/Algiers). Weeks start on Sunday.
`periodEnd`	`DateTime @db.Date`	—	—	Last day, inclusive; derived from `period` at write time and stored for range queries.
`targetValue`	`BigInt`	—	—	Count, or centimes TTC for `REVENUE` (BigInt: a monthly revenue target can exceed the Int range of 21 474 836,47 DA). Serialised as a JSON number.
`note`	`String`	yes	—	—
`createdById`	`String @db.Uuid`	—	—	FK → User · `Restrict`.
`createdAt`	`DateTime`	—	`now()`	—
`updatedAt`	`DateTime`	—	`@updatedAt`	—

Relations: delegate → DelegateProfile (delegateId, onDelete: Restrict) · createdBy → User (createdById, onDelete: Restrict).
Keys & indexes: @@unique([delegateId, metric, period, periodStart]) · @@index([periodStart, periodEnd]).
Enums: ObjectiveMetric, ObjectivePeriod.
Derived, not stored: achievement — counted from Visit (DONE), Reseller creations by the delegate, InvitationEvent APP_INSTALLED, Order (isFirstOrder, attributedDelegateId) over the period; never stored, so late syncs and corrections are always reflected.

Constraints and indexes Prisma cannot express (raw SQL in the migration):

ALTER TABLE objectives ADD CONSTRAINT objectives_values CHECK (period_end >= period_start AND target_value > 0);

Example row:

{
  "id": "01a1871a-2500-7130-bb56-7aee6bcf78ea",
  "delegateId": "01a1a024-7d34-7424-adb6-099ecaff5095",
  "metric": "VISITS",
  "period": "WEEK",
  "periodStart": "2026-11-01",
  "periodEnd": "2026-11-07",
  "targetValue": 60,
  "note": null,
  "createdById": "01a1a00a-4535-7ff7-af24-a0a3bb095b70",
  "createdAt": "2026-10-29T10:00:00.000Z",
  "updatedAt": "2026-10-29T10:00:00.000Z"
}

Tours

Tour

A delegate's planned day of visits (tournée, FLD-03/04/12). Table tours · written by field (planning by admin.tours.*; lifecycle by delegate.tours.*; 00:05 job for TO_START).

Field	Prisma type	Null	Default	Constraints / notes
`id`	`String @db.Uuid`	—	`uuid(7)`	PK.
`number`	`String @db.VarChar(20)`	—	—	Unique. `TRN-YYYY-NNNNNN` from NumberSequence `TOUR`.
`delegateId`	`String @db.Uuid`	—	—	FK → DelegateProfile · `Restrict`. Only this delegate can start it (cahier §4.5).
`tourDate`	`DateTime @db.Date`	—	—	Business day in Africa/Algiers.
`status`	`TourStatus`	—	`PLANNED`	—
`statusChangedAt`	`DateTime`	—	`now()`	—
`orderingMode`	`TourOrderingMode`	—	`RECOMMENDED`	`IMPOSED` forbids `delegate.tours.reorder`.
`name`	`String`	yes	—	—
`note`	`String`	yes	—	Instructions from the planner.
`seriesId`	`String @db.Uuid`	yes	—	Shared by the tours created together as a recurring series (no table).
`plannedStartAt`	`DateTime`	yes	—	—
`startedAt`	`DateTime`	yes	—	Device time of `delegate.tours.start`.
`startLat`	`Decimal @db.Decimal(9,6)`	yes	—	Recorded at start when GPS permission allows (cahier §6.1).
`startLng`	`Decimal @db.Decimal(9,6)`	yes	—	WGS 84 longitude, degrees.
`startAccuracyMeters`	`Int`	yes	—	—
`suspendedAt`	`DateTime`	yes	—	Current or last suspension.
`suspendReasonCode`	`String @db.VarChar(40)`	yes	—	ReferenceItem `TOUR_SUSPEND_REASON`.
`suspendComment`	`String`	yes	—	—
`resumedAt`	`DateTime`	yes	—	—
`suspendedSeconds`	`Int`	—	`0`	Accumulated suspension time, excluded from the working duration.
`closedAt`	`DateTime`	yes	—	Device time of `delegate.tours.close` (completedAt on the lifecycles page).
`endLat`	`Decimal @db.Decimal(9,6)`	yes	—	Position at closing, when available.
`endLng`	`Decimal @db.Decimal(9,6)`	yes	—	WGS 84 longitude, degrees.
`endAccuracyMeters`	`Int`	yes	—	—
`closingComment`	`String`	yes	—	End-of-day comment (cahier §6.9).
`summary`	`Json`	yes	—	Frozen closing report: `plannedVisits`, `doneVisits`, `notDoneVisits`, `unplannedVisits`, `newProspects`, `appInstalls`, `orders`, `firstOrders`, `revenueTtc`, `kmTraveled`, `conversionRateBp`, `objectives[]`. Recomputed and replaced if the tour is reopened and closed again.
`kmTraveled`	`Decimal @db.Decimal(7,1)`	yes	—	Kilometres from the tour's LocationPings (accuracy ≤ 100 m, jumps filtered), computed at closing.
`reopenedById`	`String @db.Uuid`	yes	—	FK → User · `Restrict`. Authorised reopening (`field.validate`).
`reopenedAt`	`DateTime`	yes	—	—
`reopenReason`	`String`	yes	—	—
`cancelledAt`	`DateTime`	yes	—	—
`cancelledById`	`String @db.Uuid`	yes	—	FK → User · `Restrict`.
`cancelReason`	`String`	yes	—	—
`createdById`	`String @db.Uuid`	—	—	FK → User · `Restrict`.
`version`	`Int`	—	`1`	Optimistic lock: every write does `version = version + 1 WHERE version = :expected`; 0 rows → 409 `VERSION_CONFLICT`.
`createdAt`	`DateTime`	—	`now()`	—
`updatedAt`	`DateTime`	—	`@updatedAt`	—

Relations: delegate → DelegateProfile (delegateId, onDelete: Restrict) · reopenedBy → User (reopenedById, onDelete: Restrict) · cancelledBy → User (cancelledById, onDelete: Restrict) · createdBy → User (createdById, onDelete: Restrict).
Referenced by: TourStatusChange.tourId, Visit.tourId, LocationPing.tourId.
Keys & indexes: number unique · @@index([delegateId, tourDate]) · @@index([tourDate, status]) · @@index([delegateId, updatedAt]).
Enums: TourStatus, TourOrderingMode.
Lifecycle: PLANNED → TO_START (00:05 job on tourDate) → IN_PROGRESS ⇄ SUSPENDED → COMPLETED; CANCELLED from PLANNED/TO_START by the back-office; COMPLETED → IN_PROGRESS only through admin.tours.reopen. Closing requires every visit DONE, NOT_DONE (with reason) or CANCELLED, and no pending offline items.
History: Every transition → TourStatusChange with device and receipt times.
Derived, not stored: working duration = closedAt − startedAt − suspendedSeconds; live progress (done / planned) — counted from Visit; start position missing = startLat IS NULL after start (no column).

Constraints and indexes Prisma cannot express (raw SQL in the migration):

-- one live tour per delegate and day
CREATE UNIQUE INDEX tours_one_per_day ON tours (delegate_id, tour_date) WHERE status <> 'CANCELLED';
ALTER TABLE tours ADD CONSTRAINT tours_start_pair CHECK ((start_lat IS NULL) = (start_lng IS NULL));

Example row:

{
  "id": "01a1a749-e400-7044-af7e-70456693c5fa",
  "number": "TRN-2026-000045",
  "delegateId": "01a1a024-7d34-7424-adb6-099ecaff5095",
  "tourDate": "2026-11-05",
  "status": "COMPLETED",
  "statusChangedAt": "2026-11-05T16:20:02.000Z",
  "orderingMode": "RECOMMENDED",
  "name": "Alger Centre — jeudi",
  "note": "Priorité aux nouveaux prospects de Didouche Mourad.",
  "seriesId": null,
  "plannedStartAt": "2026-11-05T07:30:00.000Z",
  "startedAt": "2026-11-05T07:41:00.000Z",
  "startLat": 36.71359,
  "startLng": 3.18312,
  "startAccuracyMeters": 9,
  "suspendedAt": null,
  "suspendReasonCode": null,
  "suspendComment": null,
  "resumedAt": null,
  "suspendedSeconds": 0,
  "closedAt": "2026-11-05T16:20:00.000Z",
  "endLat": 36.71364,
  "endLng": 3.18309,
  "endAccuracyMeters": 10,
  "closingComment": "Bonne journée, 2 nouveaux prospects intéressés.",
  "summary": {
    "plannedVisits": 12,
    "doneVisits": 10,
    "notDoneVisits": 2,
    "unplannedVisits": 1,
    "newProspects": 2,
    "appInstalls": 1,
    "orders": 3,
    "firstOrders": 1,
    "revenueTtc": 2349100,
    "kmTraveled": 64.3,
    "conversionRateBp": 3000,
    "objectives": [
      {
        "metric": "VISITS",
        "target": 12,
        "achieved": 10
      }
    ]
  },
  "kmTraveled": 64.3,
  "reopenedById": null,
  "reopenedAt": null,
  "reopenReason": null,
  "cancelledAt": null,
  "cancelledById": null,
  "cancelReason": null,
  "createdById": "01a1a00a-4535-7ff7-af24-a0a3bb095b70",
  "version": 9,
  "createdAt": "2026-11-04T16:00:00.000Z",
  "updatedAt": "2026-11-05T16:20:02.000Z"
}

TourStatusChange

Append-only history of tour statuses, with the position at start/suspend/close when available. Table tour_status_changes · written by field.

Field	Prisma type	Null	Default	Constraints / notes
`id`	`String @db.Uuid`	—	`uuid(7)`	PK.
`tourId`	`String @db.Uuid`	—	—	FK → Tour · `Cascade`.
`fromStatus`	`TourStatus`	yes	—	—
`toStatus`	`TourStatus`	—	—	—
`actorUserId`	`String @db.Uuid`	yes	—	FK → User · `Restrict`. `null` for the scheduler.
`reasonCode`	`String @db.VarChar(40)`	yes	—	`TOUR_SUSPEND_REASON` code, cancel reason…
`comment`	`String`	yes	—	—
`lat`	`Decimal @db.Decimal(9,6)`	yes	—	WGS 84 latitude, degrees.
`lng`	`Decimal @db.Decimal(9,6)`	yes	—	WGS 84 longitude, degrees.
`accuracyMeters`	`Int`	yes	—	—
`occurredAt`	`DateTime`	—	—	Device time of the action (offline-safe, plausibility-checked).
`createdAt`	`DateTime`	—	`now()`	Server receipt time (receivedAt).

Relations: tour → Tour (tourId, onDelete: Cascade) · actorUser → User (actorUserId, onDelete: Restrict).
Keys & indexes: @@index([tourId, occurredAt]).
Enums: TourStatus.

Example row:

{
  "id": "01a1aaa7-7298-7b89-9f7b-a14df01ae3a7",
  "tourId": "01a1a749-e400-7044-af7e-70456693c5fa",
  "fromStatus": "TO_START",
  "toStatus": "IN_PROGRESS",
  "actorUserId": "01a1a024-7d34-7424-adb6-099ecaff5095",
  "reasonCode": null,
  "comment": null,
  "lat": 36.71359,
  "lng": 3.18312,
  "accuracyMeters": 9,
  "occurredAt": "2026-11-05T07:41:00.000Z",
  "createdAt": "2026-11-05T07:41:03.000Z"
}

A stop at a point of sale: plan, GPS check-in, timing, outcome (FLD-06…FLD-09). Table visits · written by field (planned by admin.tours.setStops; executed by delegate.visits.*; ad-hoc by delegate.tours.addVisit).

Field	Prisma type	Null	Default	Constraints / notes
`id`	`String @db.Uuid`	—	`uuid(7)`	PK. Client-generated UUIDv7 accepted (offline creation); the server upserts idempotently.
`number`	`String @db.VarChar(20)`	—	—	Unique. `VIS-YYYY-NNNNNN` from NumberSequence `VISIT`, assigned when the server first receives the visit.
`tourId`	`String @db.Uuid`	yes	—	FK → Tour · `Restrict`. `null` for a visit logged outside a tour.
`resellerId`	`String @db.Uuid`	—	—	FK → Reseller · `Restrict`.
`delegateId`	`String @db.Uuid`	—	—	FK → DelegateProfile · `Restrict`.
`visitTypeId`	`String @db.Uuid`	—	—	FK → VisitType · `Restrict`.
`sequence`	`Int`	yes	—	Position in the tour (1-based); rewritten by reorder.
`plannedAt`	`DateTime`	yes	—	Start of the time slot.
`plannedEndAt`	`DateTime`	yes	—	End of the time slot.
`priority`	`VisitPriority`	—	`NORMAL`	—
`isUnplanned`	`Boolean`	—	`false`	Added by the delegate during the tour; excluded from planned in the visits-done / visits-planned KPI.
`status`	`VisitStatus`	—	`PLANNED`	—
`statusChangedAt`	`DateTime`	—	`now()`	—
`checkedInAt`	`DateTime`	yes	—	Device time of the arrival (cahier §6.4).
`checkInLat`	`Decimal @db.Decimal(9,6)`	yes	—	WGS 84 latitude, degrees.
`checkInLng`	`Decimal @db.Decimal(9,6)`	yes	—	WGS 84 longitude, degrees.
`checkInAccuracyMeters`	`Int`	yes	—	—
`distanceMeters`	`Int`	yes	—	Haversine distance from the check-in to the reseller's GPS.
`gpsValidated`	`Boolean`	yes	—	`distanceMeters ≤ field.gpsToleranceMeters` (150) and accuracy ≤ `field.gpsMaxAccuracyMeters` (100); `null` when the reseller has no GPS.
`checkInReceivedAt`	`DateTime`	yes	—	Server receipt time of the check-in (late syncs are flagged in BO-25).
`startedAt`	`DateTime`	yes	—	—
`endedAt`	`DateTime`	yes	—	—
`durationSeconds`	`Int`	yes	—	`endedAt − startedAt`, written at end. Stored (although derivable) because every field KPI aggregates it.
`notDoneAt`	`DateTime`	yes	—	—
`notDoneLat`	`Decimal @db.Decimal(9,6)`	yes	—	Position when the visit was declared not done, when available.
`notDoneLng`	`Decimal @db.Decimal(9,6)`	yes	—	WGS 84 longitude, degrees.
`notDoneReasonCode`	`String @db.VarChar(40)`	yes	—	ReferenceItem `VISIT_NOT_DONE_REASON`; required for `NOT_DONE` (FLD-09).
`notDoneComment`	`String`	yes	—	Required when the reason has `requiresComment`.
`note`	`String @db.VarChar(300)`	yes	—	Quick note (`delegate.visits.note`).
`lockedAt`	`DateTime`	yes	—	Set at `DONE`/`NOT_DONE`; a locked visit refuses delegate edits (cahier §6.4).
`unlockedUntil`	`DateTime`	yes	—	Set by `admin.visits.unlock` to now + `field.visitUnlockHours` (24); the delegate may edit until then or until the next `end` (audited).
`unlockedById`	`String @db.Uuid`	yes	—	FK → User · `Restrict`.
`cancelledAt`	`DateTime`	yes	—	—
`occurredAt`	`DateTime`	yes	—	Device creation time of an ad-hoc visit created offline; `null` for planned visits.
`createdById`	`String @db.Uuid`	—	—	FK → User · `Restrict`. Planner, or the delegate for ad-hoc visits.
`version`	`Int`	—	`1`	Optimistic lock: every write does `version = version + 1 WHERE version = :expected`; 0 rows → 409 `VERSION_CONFLICT`.
`createdAt`	`DateTime`	—	`now()`	—
`updatedAt`	`DateTime`	—	`@updatedAt`	—

Relations: tour → Tour (tourId, onDelete: Restrict) · reseller → Reseller (resellerId, onDelete: Restrict) · delegate → DelegateProfile (delegateId, onDelete: Restrict) · visitType → VisitType (visitTypeId, onDelete: Restrict) · unlockedBy → User (unlockedById, onDelete: Restrict) · createdBy → User (createdById, onDelete: Restrict).
Referenced by: ResellerPhoto.visitId, Order.visitId, VisitChecklistItem.visitId, VisitPhoto.visitId, VisitReport.visitId.
Keys & indexes: number unique · @@index([delegateId, plannedAt]) · @@index([tourId, sequence]) · @@index([resellerId, createdAt(sort: Desc)]) · @@index([status, plannedAt]) · @@index([delegateId, updatedAt]).
Enums: VisitPriority, VisitStatus.
Lifecycle: PLANNED → CHECKED_IN → IN_PROGRESS → DONE; PLANNED/CHECKED_IN → NOT_DONE (reason) ; PLANNED → CANCELLED (stop removed or tour cancelled). DONE requires the photos and report demanded by the VisitType.
History: Each action is an AuditLog entry whose at is the device occurredAt and createdAt the receipt time; the visit row keeps the latest state.
Derived, not stored: En retard (late) — status = PLANNED AND plannedEndAt < now() (enums.json note); distance and ETA from the delegate's current position — computed on the device.

Constraints and indexes Prisma cannot express (raw SQL in the migration):

-- one live stop per point of sale in a tour
CREATE UNIQUE INDEX visits_one_stop_per_tour ON visits (tour_id, reseller_id)
  WHERE tour_id IS NOT NULL AND status <> 'CANCELLED';
ALTER TABLE visits ADD CONSTRAINT visits_not_done_reason CHECK (status <> 'NOT_DONE' OR not_done_reason_code IS NOT NULL);
ALTER TABLE visits ADD CONSTRAINT visits_checkin_pair CHECK ((check_in_lat IS NULL) = (check_in_lng IS NULL));

Example row:

{
  "id": "01a1a749-e400-7ac8-8db4-22bf1c830102",
  "number": "VIS-2026-001234",
  "tourId": "01a1a749-e400-7044-af7e-70456693c5fa",
  "resellerId": "01a19ba2-2fe0-7300-a148-73d1a4ef38c0",
  "delegateId": "01a1a024-7d34-7424-adb6-099ecaff5095",
  "visitTypeId": "01a15db0-5680-789f-baff-db854f5eb976",
  "sequence": 3,
  "plannedAt": "2026-11-05T09:00:00.000Z",
  "plannedEndAt": "2026-11-05T09:30:00.000Z",
  "priority": "HIGH",
  "isUnplanned": false,
  "status": "DONE",
  "statusChangedAt": "2026-11-05T09:31:02.000Z",
  "checkedInAt": "2026-11-05T09:02:10.000Z",
  "checkInLat": 36.773201,
  "checkInLng": 3.05899,
  "checkInAccuracyMeters": 8,
  "distanceMeters": 18,
  "gpsValidated": true,
  "checkInReceivedAt": "2026-11-05T09:02:12.000Z",
  "startedAt": "2026-11-05T09:03:00.000Z",
  "endedAt": "2026-11-05T09:31:00.000Z",
  "durationSeconds": 1680,
  "notDoneAt": null,
  "notDoneLat": null,
  "notDoneLng": null,
  "notDoneReasonCode": null,
  "notDoneComment": null,
  "note": "Gérant intéressé par toute la gamme câbles.",
  "lockedAt": "2026-11-05T09:31:00.000Z",
  "unlockedUntil": null,
  "unlockedById": null,
  "cancelledAt": null,
  "occurredAt": null,
  "createdById": "01a1a00a-4535-7ff7-af24-a0a3bb095b70",
  "version": 6,
  "createdAt": "2026-11-04T16:00:00.000Z",
  "updatedAt": "2026-11-05T09:31:02.000Z"
}

VisitType

Configures what a visit requires: photos, report, checklist and which report fields are shown (FLD-16, cahier §6.4/§6.8). Table visit_types · written by field (seeded; admin.visitTypes.crud, BO-26).

Field	Prisma type	Null	Default	Constraints / notes
`id`	`String @db.Uuid`	—	`uuid(7)`	PK.
`code`	`String @db.VarChar(30)`	—	—	Unique.
`name`	`String`	—	—	—
`description`	`String`	yes	—	—
`requiresPhoto`	`Boolean`	—	`false`	—
`minPhotos`	`Int`	—	`0`	Minimum VisitPhoto rows before `end` (≥ 1 when `requiresPhoto`).
`requiresReport`	`Boolean`	—	`false`	Report mandatory before `end` (cahier §6.8).
`checklistTemplateId`	`String @db.Uuid`	yes	—	FK → ChecklistTemplate · `Restrict`.
`reportFields`	`Json`	—	`{}`	Per built-in report field (`personMet`, `interestLevel`, `productsPresented`, `promotionsPresented`, `issueTags`, `nextAction`, `followUpAt`, `comment`, `attachments`): `required`, `optional` or `hidden`; Zod schema `VisitReportFieldConfig`.
`defaultDurationMinutes`	`Int`	yes	—	Default slot length when planning.
`sortOrder`	`Int`	—	`0`	—
`isActive`	`Boolean`	—	`true`	—
`archivedAt`	`DateTime`	yes	—	—
`createdAt`	`DateTime`	—	`now()`	—
`updatedAt`	`DateTime`	—	`@updatedAt`	—

Relations: checklistTemplate → ChecklistTemplate (checklistTemplateId, onDelete: Restrict).
Referenced by: Visit.visitTypeId.
Keys & indexes: code unique.

Constraints and indexes Prisma cannot express (raw SQL in the migration):

ALTER TABLE visit_types ADD CONSTRAINT visit_types_photos CHECK (min_photos >= 0 AND (NOT requires_photo OR min_photos >= 1));

Example row:

{
  "id": "01a15db0-5680-789f-baff-db854f5eb976",
  "code": "PROSPECTION",
  "name": "Prospection",
  "description": "Première visite d'un point de vente non client.",
  "requiresPhoto": true,
  "minPhotos": 1,
  "requiresReport": true,
  "checklistTemplateId": "01a15db0-5680-7571-b1e6-4a55a41adec8",
  "reportFields": {
    "personMet": "required",
    "interestLevel": "required",
    "productsPresented": "optional",
    "promotionsPresented": "optional",
    "issueTags": "optional",
    "nextAction": "required",
    "followUpAt": "optional",
    "comment": "optional",
    "attachments": "hidden"
  },
  "defaultDurationMinutes": 30,
  "sortOrder": 1,
  "isActive": true,
  "archivedAt": null,
  "createdAt": "2026-10-21T09:00:00.000Z",
  "updatedAt": "2026-10-21T09:00:00.000Z"
}

ChecklistTemplate

A commercial checklist (cahier §6.4) whose items have stable keys. Table checklist_templates · written by field (admin.checklists.crud).

Field	Prisma type	Null	Default	Constraints / notes
`id`	`String @db.Uuid`	—	`uuid(7)`	PK.
`code`	`String @db.VarChar(30)`	—	—	Unique.
`name`	`String`	—	—	—
`items`	`Json`	—	—	Ordered array of `key` (stable, snake_case), `label`, optional `labelAr`, `type` (`CHECK`, `NUMBER` or `TEXT`), `required`; Zod schema `ChecklistItems`. Keys are never reused for another meaning.
`itemsVersion`	`Int`	—	`1`	Incremented on every edit of `items`; answers keep the version they were given against (not an optimistic lock).
`isActive`	`Boolean`	—	`true`	—
`archivedAt`	`DateTime`	yes	—	—
`createdAt`	`DateTime`	—	`now()`	—
`updatedAt`	`DateTime`	—	`@updatedAt`	—

Relations: none outgoing.
Referenced by: VisitType.checklistTemplateId, VisitChecklistItem.templateId.
Keys & indexes: code unique.

Example row:

{
  "id": "01a15db0-5680-7571-b1e6-4a55a41adec8",
  "code": "MERCH-STD",
  "name": "Contrôle merchandising standard",
  "items": [
    {
      "key": "display_present",
      "label": "Présentoir HOCO en place",
      "type": "CHECK",
      "required": true
    },
    {
      "key": "prices_displayed",
      "label": "Prix affichés",
      "type": "CHECK",
      "required": false
    },
    {
      "key": "shelf_facings",
      "label": "Nombre de facings HOCO",
      "type": "NUMBER",
      "required": false
    },
    {
      "key": "competitors",
      "label": "Marques concurrentes visibles",
      "type": "TEXT",
      "required": false
    }
  ],
  "itemsVersion": 2,
  "isActive": true,
  "archivedAt": null,
  "createdAt": "2026-10-21T09:00:00.000Z",
  "updatedAt": "2026-10-21T09:00:00.000Z"
}

VisitChecklistItem

The answer to one checklist item during a visit. Table visit_checklist_items · written by field (delegate.visits.checklist).

Field	Prisma type	Null	Default	Constraints / notes
`id`	`String @db.Uuid`	—	`uuid(7)`	PK.
`visitId`	`String @db.Uuid`	—	—	FK → Visit · `Cascade`.
`templateId`	`String @db.Uuid`	—	—	FK → ChecklistTemplate · `Restrict`.
`templateVersion`	`Int`	—	—	`ChecklistTemplate.itemsVersion` answered against.
`itemKey`	`String @db.VarChar(60)`	—	—	—
`label`	`String`	—	—	Snapshot of the label answered.
`isChecked`	`Boolean`	yes	—	`CHECK` items.
`numberValue`	`Int`	yes	—	`NUMBER` items.
`textValue`	`String`	yes	—	`TEXT` items.
`answeredAt`	`DateTime`	yes	—	Device time.
`createdAt`	`DateTime`	—	`now()`	—
`updatedAt`	`DateTime`	—	`@updatedAt`	—

Relations: visit → Visit (visitId, onDelete: Cascade) · template → ChecklistTemplate (templateId, onDelete: Restrict).
Keys & indexes: @@unique([visitId, itemKey]).
PUT …/checklist upserts the whole set idempotently; refused once the visit is locked.

Example row:

{
  "id": "01a1aaf8-e240-792b-84a8-a58cfa3bf294",
  "visitId": "01a1a749-e400-7ac8-8db4-22bf1c830102",
  "templateId": "01a15db0-5680-7571-b1e6-4a55a41adec8",
  "templateVersion": 2,
  "itemKey": "display_present",
  "label": "Présentoir HOCO en place",
  "isChecked": true,
  "numberValue": null,
  "textValue": null,
  "answeredAt": "2026-11-05T09:10:00.000Z",
  "createdAt": "2026-11-05T09:10:00.000Z",
  "updatedAt": "2026-11-05T09:10:00.000Z"
}

VisitPhoto

A photo taken as visit proof, with capture time and position (cahier §6.4). Table visit_photos · written by field (delegate.visits.addPhoto).

Field	Prisma type	Null	Default	Constraints / notes
`id`	`String @db.Uuid`	—	`uuid(7)`	PK. Client-generated UUIDv7 accepted (offline creation); the server upserts idempotently.
`visitId`	`String @db.Uuid`	—	—	FK → Visit · `Cascade`.
`fileId`	`String @db.Uuid`	—	—	Unique. FK → File · `Restrict`. Purpose `VISIT_PHOTO`.
`kind`	`PhotoKind`	—	—	—
`caption`	`String`	yes	—	—
`occurredAt`	`DateTime`	—	—	Device capture time.
`lat`	`Decimal @db.Decimal(9,6)`	yes	—	WGS 84 latitude, degrees.
`lng`	`Decimal @db.Decimal(9,6)`	yes	—	WGS 84 longitude, degrees.
`accuracyMeters`	`Int`	yes	—	—
`createdAt`	`DateTime`	—	`now()`	Server receipt time.

Relations: visit → Visit (visitId, onDelete: Cascade) · file → File (fileId, onDelete: Restrict).
Keys & indexes: fileId unique · @@index([visitId]).
Enums: PhotoKind.
A FACADE visit photo of a reseller without a primary façade is also attached as a ResellerPhoto (same File).

Example row:

{
  "id": "01a1aafa-b700-797d-8975-b05f7ca422ee",
  "visitId": "01a1a749-e400-7ac8-8db4-22bf1c830102",
  "fileId": "01a19afc-14f8-7c5a-97c7-2948f02303f5",
  "kind": "SHELF",
  "caption": "Linéaire câbles après mise en place",
  "occurredAt": "2026-11-05T09:11:48.000Z",
  "lat": 36.77319,
  "lng": 3.05897,
  "accuracyMeters": 7,
  "createdAt": "2026-11-05T09:12:00.000Z"
}

VisitReport

The structured visit report (cahier §6.8, FLD-10). Table visit_reports · written by field (delegate.visits.report); creates or updates the follow-up CrmTask.

Field	Prisma type	Null	Default	Constraints / notes
`id`	`String @db.Uuid`	—	`uuid(7)`	PK. Client-generated UUIDv7 accepted (offline creation); the server upserts idempotently.
`visitId`	`String @db.Uuid`	—	—	Unique. FK → Visit · `Cascade`. One report per visit.
`personMetName`	`String`	yes	—	—
`personMetRoleCode`	`String @db.VarChar(40)`	yes	—	ReferenceItem `PERSON_ROLE`.
`interestLevel`	`InterestLevel`	yes	—	—
`productsPresented`	`String[] @db.Uuid`	—	`[]`	Product ids (no FK: array).
`promotionsPresented`	`String[] @db.Uuid`	—	`[]`	Promotion ids (no FK: array).
`issueTagCodes`	`String[]`	—	`[]`	ReferenceItem `VISIT_ISSUE_TAG` codes (problems and needs).
`needs`	`String`	yes	—	The store's needs in the delegate's words (≤ 1 000 in the API, DA-11), trimmed; a visit type can require it (`reportFields.needs`) — SRV-42.
`nextAction`	`NextAction`	—	`NONE`	—
`followUpAt`	`DateTime`	yes	—	Due date of the follow-up task; required unless `nextAction = NONE`.
`comment`	`String`	yes	—	—
`attachmentFileIds`	`String[] @db.Uuid`	—	`[]`	Extra attachments (`VISIT_PHOTO` or `STORE_DOCUMENT` files of the same uploader; no FK: array).
`occurredAt`	`DateTime`	—	—	Device time the report was saved.
`createdAt`	`DateTime`	—	`now()`	Server receipt time.
`updatedAt`	`DateTime`	—	`@updatedAt`	—

Relations: visit → Visit (visitId, onDelete: Cascade).
Referenced by: CrmTask.visitReportId.
Keys & indexes: visitId unique.
Enums: InterestLevel, NextAction.
The task link lives on CrmTask (visitReportId, unique). The report is visible in the reseller 360° and in delegate.reports.

Constraints and indexes Prisma cannot express (raw SQL in the migration):

ALTER TABLE visit_reports ADD CONSTRAINT visit_reports_follow_up CHECK (next_action = 'NONE' OR follow_up_at IS NOT NULL);

Example row:

{
  "id": "01a1ab0b-31c0-7562-b848-5d61818ed475",
  "visitId": "01a1a749-e400-7ac8-8db4-22bf1c830102",
  "personMetName": "Amine Benali",
  "personMetRoleCode": "OWNER",
  "interestLevel": "HIGH",
  "productsPresented": [
    "01a18298-9480-70cc-80a7-8ddb761bc4d8"
  ],
  "promotionsPresented": [
    "01a19656-4a80-7734-8064-1c5352836363"
  ],
  "issueTagCodes": [
    "NEEDS_DISPLAY",
    "PRICE_TOO_HIGH"
  ],
  "nextAction": "PHONE_FOLLOW_UP",
  "followUpAt": "2026-11-09T08:00:00.000Z",
  "comment": "Veut un présentoir avant de commander les écouteurs.",
  "attachmentFileIds": [],
  "occurredAt": "2026-11-05T09:29:40.000Z",
  "createdAt": "2026-11-05T09:30:00.000Z",
  "updatedAt": "2026-11-05T09:30:00.000Z"
}

Positions

LocationPing

A position sample of a delegate during an IN_PROGRESS tour (FLD-13, FLD-15), every 5 min or 200 m by default. Table location_pings · written by field (sync.locationPings, batches).

Field	Prisma type	Null	Default	Constraints / notes
`id`	`String @db.Uuid`	—	`uuid(7)`	PK (server-generated).
`delegateId`	`String @db.Uuid`	—	—	FK → DelegateProfile · `Restrict`.
`tourId`	`String @db.Uuid`	yes	—	FK → Tour · `Restrict`.
`lat`	`Decimal @db.Decimal(9,6)`	—	—	WGS 84 latitude, degrees.
`lng`	`Decimal @db.Decimal(9,6)`	—	—	WGS 84 longitude, degrees.
`accuracyMeters`	`Int`	—	—	—
`speedMps`	`Decimal @db.Decimal(5,2)`	yes	—	Metres per second as reported by the OS.
`headingDeg`	`Int`	yes	—	—
`batteryPct`	`Int`	yes	—	—
`isMocked`	`Boolean`	yes	—	Android mock-location flag; surfaced on BO-23, never used to reject.
`occurredAt`	`DateTime`	—	—	Device sampling time.
`createdAt`	`DateTime`	—	`now()`	Server receipt time.

Relations: delegate → DelegateProfile (delegateId, onDelete: Restrict) · tour → Tour (tourId, onDelete: Restrict).
Keys & indexes: @@unique([delegateId, occurredAt]) · @@index([tourId, occurredAt]).
Idempotent batches: the unique (delegateId, occurredAt) makes a replayed batch insert nothing (ON CONFLICT DO NOTHING).
Retention & partitioning: rows older than field.locationRetentionDays (90, proposal) are deleted nightly in batches of 10 000 using the BRIN index. Expected volume (≈ 50 delegates × ≈ 120 pings/day) stays under 1 M rows at 90 days, so the table starts unpartitioned; switch to monthly range partitions on occurred_at (drop partition instead of delete) if it ever exceeds ≈ 50 M rows. Prisma does not manage partitions: that would be raw SQL.
Only written while the tour is IN_PROGRESS and the delegate has a current locationConsentAt; pings outside are rejected.

Constraints and indexes Prisma cannot express (raw SQL in the migration):

-- purge and range scans on a large append-only table
CREATE INDEX location_pings_occurred_brin ON location_pings USING brin (occurred_at);

Example row:

{
  "id": "01a1aaf4-4e60-7e56-90b5-4495a01a7453",
  "delegateId": "01a1a024-7d34-7424-adb6-099ecaff5095",
  "tourId": "01a1a749-e400-7044-af7e-70456693c5fa",
  "lat": 36.77295,
  "lng": 3.05741,
  "accuracyMeters": 11,
  "speedMps": 0.4,
  "headingDeg": 212,
  "batteryPct": 64,
  "isMocked": false,
  "occurredAt": "2026-11-05T09:05:00.000Z",
  "createdAt": "2026-11-05T09:05:00.000Z"
}

ObjectivesCopy Anchor Link

ObjectiveCopy Anchor Link

ToursCopy Anchor Link

TourCopy Anchor Link

TourStatusChangeCopy Anchor Link

VisitsCopy Anchor Link

VisitCopy Anchor Link

VisitTypeCopy Anchor Link

ChecklistTemplateCopy Anchor Link

VisitChecklistItemCopy Anchor Link

VisitPhotoCopy Anchor Link

VisitReportCopy Anchor Link

PositionsCopy Anchor Link

LocationPingCopy Anchor Link

On this page

Objectives

Objective

Tours

Tour

TourStatusChange

Visits

Visit

VisitType

ChecklistTemplate

VisitChecklistItem

VisitPhoto

VisitReport

Positions

LocationPing