dash-app — full API data model

Every FastAPI service in services/, the routes it exposes, the tables it touches, and how those tables connect. Source of truth: services/*/src/web/ + common/dori_model/.

Contents

Services overview

ServicePrefix(es)PurposeAuth model
config_app/common /infra /ops /appTenant/user/CRUD; ICD, COD, DAC dashboardsJWT bearer, tenant + admin gates
monitor/monitorSystem events (MLC_DOWN etc.) — dispatches messagesJWT bearer, tenant
analytics_app/analyticsInference-event search, counts, snippet fetchJWT bearer, tenant
warehouse_app/warehousePallet scan ingestion + WMS dashboardsJWT bearer, tenant
dim/dimDashboard Inference Module — WebSocket fan-out of live eventsToken via query string on WS
cim/cimCallbacks, event/message dispatch, kit callback, plugin uploadJWT bearer, tenant
dmf/dmfData Management Framework — S3-backed media, images, part-imagesJWT bearer, tenant
job_app/jobsStart/stop/monitor batch + live inference jobsJWT bearer, tenant
Auth flow: POST /config/common/login → JWT → TenantContext{company_id, user_id, user_role} is decoded on every request. require_admin extra-gates on user_role ∈ {DORI_ADMIN, ADMIN}.

config_app

/common

shared auth + self-servicetenant
POST/loginapp_user (verify)
POST/update_passwordapp_user (self)
GET/userapp_user WHERE company_id = ctx.company_id
POST/userapp_user INSERT with company_id = ctx.company_id admin
DELETE/user/{id}app_user — tenant-guarded admin

/infra

ICD (platform admin) platform
GET/companycompany
POST/companycompany + optional first app_user admin (one txn)
DELETE/company/{id}company — CASCADE removes dependents
GET/userapp_user — all tenants; opt ?company_id=
POST/userapp_user INSERT under explicit company_id
DELETE/user/{id}app_user (any tenant)
GET/credentialsd_auth_aws WHERE company_id = ctx.company_id
POST/credentialsd_auth_aws — upsert for ctx.company_id
GET/mlcmlc platform
POST/mlcmlc
DELETE/mlc/{id}mlc
GET/mlc_configmlc_config WHERE company_id = ctx.company_id; opt ?mlc_id=
POST/mlc_configmlc_config — FK-checks mlc_id
PATCH/mlc_config/{id}mlc_config — tenant-guarded partial update
DELETE/mlc_config/{id}mlc_config
GET/clientclient + client_user junction platform
POST/clientclient + optional client_user
DELETE/client/{id}client
GET/company_configcompany_config — tenant k/v
POST/company_configcompany_config — upsert
GET/service_bundleservice_bundle platform
POST/service_bundleservice_bundle
DELETE/service_bundle/{id}service_bundle
POST/system_event501 stub — use /monitor/system_event/

/ops

COD (customer ops) tenant
GET/locationslocation
POST/locationslocation
DELETE/locations/{id}location
GET/subscribersubscriber
POST/subscribersubscriber
GET/event_typeevent_type platform catalog
GET/employeesapp_user
GET/client_userapp_user — same table, different shape from /employees
GET/streamdevice JOIN location
POST/streamdevice — guards location tenant
DELETE/stream/{id}device
GET/subscriptionssubscriber_event
POST/subscriptionssubscriber_event

/app

DAC (catalog) tenant
GET/partpart
POST/partpart
DELETE/part/{id}part
POST/part_image501 stub → DMF /dmf/part_image
GET/dynamic_kitdynamic_kit
POST/dynamic_kitdynamic_kit
DELETE/dynamic_kit/{id}dynamic_kit
POST/dynamic_kit_updatedynamic_kit — scan_status callback by sku

monitor

System-event ingest. Fires when infra components (like MLCs) report state changes; matches active subscriptions and writes to message_log.

POST/system_event/reads subscriber_event, subscriber; writes message_log. Optionally dispatches SMS/email/webhook.

analytics_app

All inference-event reads happen here. Time-series data lives in the inference schema (TimescaleDB hypertables). Tenant scoping is implicit: the time-series tables carry no company_id — queries scope via request_id → job → device → location → company_id.

POST/get_eventsinference_event timescale
POST/get_last_eventsinference_event
POST/get_events_combinedinference_event + inference_event_image
POST/event_countsinference_event grouped by event_type/camera_id
POST/search_eventsinference_event
POST/get_raw_snippetsinference_detectobject_predicted
POST/clove_dental/list_statusinference_event — Clove-specific view
POST/clove_dental/update_statusinference_event — is_overridden write
GET / POST/event_noteevent notes (see event_note in canonical schema)
GET/get_imageS3 — proxied from d_auth_aws creds
GET/v2/get_fileS3

warehouse_app

WMS pallet-scan ingestion and dashboards. Every row of pallet_result is tied to a camera and denormalizes company_id for fast tenant queries.

POST/pallet_result/pallet_result INSERT (camera → device tenant guard)
POST/pallet_result/listpallet_result WHERE company_id = ctx.company_id
POST/pallet_result/{id}/ackpallet_result — mark verdict acknowledged
GET/dashboard/statspallet_result — pass/fail/warn rollup
GET/dashboard/throughputpallet_result — histogram over time
GET/dashboard/supplierspallet_result — per-supplier scorecard
GET/dashboard/dockspallet_result JOIN device JOIN location

dim

Dashboard Inference Module. Listens to Postgres NOTIFY triggers on system-event tables and fans them out over WebSocket to dashboards. No writes.

WS/ws/notifications/{device}listens on message_log / inference_event NOTIFY, streams JSON frames
GET/statsin-memory: connected clients per company/device

cim

Communication + Integration Module. Receives inference-event callbacks, matches them against active subscriptions, and dispatches webhooks / SMS / email. Also handles dynamic-kit callbacks and plugin uploads.

POST/event_callback/reads subscriber_event JOIN job; writes message_log
POST/event/inject an inference-event style payload (used by MLC callbacks)
POST/message/direct message dispatch (SMS/email); writes message_log
POST/kit_callback/reads company_config; updates dynamic_kit.scan_status
POST/upload_pluginuploads plugin artifacts S3
POST/publisher/fans an event out to registered publishers

dmf

Data Management Framework — the S3-backed media layer. 24 endpoints, most persist to S3 via d_auth_aws creds and record metadata in image/part_image.

POST/capture_image, /capture_videoS3 + image
GET / POST / DELETE/mediaimage
GET / POST/imagesimage WHERE company_id = ctx.company_id
GET/image_detailimage + part_image
POST/image_comment, /kit_comment, /prediction_commentupdate metadata JSON on image / dynamic_kit
GET/labelslabel catalog (usually event_type)
POST/loginapp_user (verify)
GET/locationlocation
GET / POST/partpart
POST/part_imagepart_image junction + image
GET/streamdevice
GET / POST/user_cameraper-user camera bindings (metadata on app_user/device)
GET/job, /job_mediajob
GET/cvat, /cvat_orgs501 stubs — CVAT bridge unimplemented

job_app

Everything that mutates job. Jobs are platform-wide (job has no company_id) — tenant scope comes from job.device_id → device.location_id → location.company_id.

POST/start_job, /start_live_jobjob INSERT — with device_id, mlc_id, service_bundle_id
POST/stop_job, /stop_live_jobsjob — set end_time, status
DELETE/v2/job/{request_id}job — soft-delete via is_deleted
GET/getjobslistjob JOIN device JOIN location (tenant filter)
POST/start_batch_job, /stop_batch_jobjob — batch flavour
POST/file_upload, /upload_snippet, /upload_thumbnailS3 — snippet/thumbnail artifacts
GET / POST / DELETE/dynamic_kit, /dynamic_kit_updatedynamic_kit (mirrors /app/dynamic_kit)
POST/clove_dental/clove_trigger_events, /start_triggerjob — trigger-based batch
GET/auth_callbackOAuth callback landing
POST/v2/add_benchmark_result, /call_back_serverjob — benchmark/eval callbacks

Entity relationships

                          ┌─────────────────┐
                          │   company     │  tenant root
                          │  id (PK)        │
                          └────────┬────────┘
                                   │  company_id
       ┌───────────────┬───────────┼──────────────┬────────────────┬──────────────┐
       │               │           │              │                │              │
       ▼               ▼           ▼              ▼                ▼              ▼
 ┌───────────┐  ┌────────────┐ ┌─────────┐ ┌─────────────┐ ┌──────────────┐ ┌──────────┐
 │  app_user   │  │d_company_  │ │d_auth_  │ │subscriber │ │location    │ │  part  │
 │           │  │  config    │ │  aws    │ │             │ │  (denorm     │ │d_dynamic_│
 │d_company_ │  │(k/v)       │ │(S3/SNS) │ │             │ │   company) │ │   kit    │
 │  id (FK)  │  └────────────┘ └─────────┘ └──────┬──────┘ └──────┬───────┘ └────┬─────┘
 │user_role  │                                    │ d_subscriber_ │              │
 │  {DORI_   │                                    │      id       │ location_id│
 │  ADMIN,   │                                    ▼               ▼              │
 │  ADMIN,   │                             ┌─────────────┐  ┌──────────┐         │
 │  USER}    │                             │d_subscriber_│  │ device │         │
 └─────┬─────┘                             │   event     │  │          │◀────┐   │
       │                                   │(webhook cfg)│  │mlc_id  │     │   │
       │                                   └──────┬──────┘  │d_service_│     │   │
       │ user_id           subscriber_id      │         │ bundle_id│     │   │
       ▼                     ┌───────────────┐    │         └────┬─┬───┘     │   │
 ┌─────────────┐             │ message_log │◀───┘              │ │         │   │
 │client_user│──▶┌────────┐│ (denorm co,   │                   │ │         │   │
 │  (junction) │   │client││  loc, dev)    │                   │ │         │   │
 └─────────────┘   │(platfm)│└───────────────┘                   │ │         │   │
                   └────────┘                                    │ │         │   │
                                                                 │ │         │   │
                       ┌─────────────────┐                       │ │         │   │
                       │service_bundle │◀──────────────────────┘ │         │   │
                       │  (platform)     │◀─────┐                  │         │   │
                       └─────────────────┘      │                  │         │   │
                                                │ d_service_       │         │   │
                                                │  bundle_id       │         │   │
                                       ┌────────┴───┐              │         │   │
                                       │   mlc    │◀─────────────┘         │   │
                                       │ (platform) │                        │   │
                                       └─────┬──────┘                        │   │
                                             │ mlc_id                      │   │
                                             ▼                               │   │
                                    ┌────────────────┐                       │   │
                                    │ mlc_config   │                       │   │
                                    │(per MLC per co)│                       │   │
                                    │ company_id   │◀── tenant-scoped      │   │
                                    │ mlc_id       │                       │   │
                                    │ predictdb_*    │                       │   │
                                    │ eventdb_*      │                       │   │
                                    │ fps, nms_conf  │                       │   │
                                    └────────────────┘                       │   │
                                                                             │   │
    ┌─────────────────────────────────────── device_id ─────────────────┐  │   │
    │                                                                    │  │   │
    ▼                                                                    │  │   │
 ┌──────────────┐                                                        │  │   │
 │    job     │                                                        │  │   │
 │ (platform;   │                                                        │  │   │
 │  scope via   │                                                        │  │   │
 │  device→   │                                                        │  │   │
 │  location→co)│                                                        │  │   │
 │              │                                                        │  │   │
 │ mlc_id ────┼─── FK ────────────────────────────────────────────────▶ │  │   │
 │ d_bundle_id ─┼─── FK ─────────────────────────▶ service_bundle      │  │   │
 │ request_id   │                                                        │  │   │
 └──────┬───────┘                                                        │  │   │
        │ request_id (soft link, no FK)                                  │  │   │
        ▼                                                                │  │   │
 ┌────────────────────────────────────────────┐                          │  │   │
 │  inference.inference_event│  Timescale hypertable    │  │   │
 │  inference_detectobject_predicted        │  NO company_id         │  │   │
 │  inference_runtimestat                   │  (scope via job→device)  │  │   │
 └────────────────────────────────────────────┘                          │  │   │
                                                                         │  │   │
    ┌──────────────────┐                                                 │  │   │
    │ pallet_result  │◀─── device_id ────────────────────────────────┘  │   │
    │ (denorm co, loc) │◀─── location_id ─────────────────────────────────┘   │
    └──────────────────┘◀─── company_id ───────────────────────────────────────┘

 Other tables:
   image, part_image         — S3 metadata, joined via part
   dynamic_kit_part            — dynamic_kit × part junction (qty, confidence)
   event_type                  — platform label catalog (no tenant)

Tenant vs platform-wide vs time-series

Tenant-scoped company_id column

  • app_user
  • company_config
  • d_auth_aws
  • subscriber
  • subscriber_event
  • message_log (denormalized)
  • location (denormalized)
  • part
  • dynamic_kit
  • mlc_config
  • pallet_result (denormalized)

Platform-wide no company_id

  • company (the root itself)
  • mlc
  • service_bundle
  • client, client_user
  • event_type (label catalog)
  • device — scope via location
  • job — scope via device → location
  • image, part_image, dynamic_kit_part — scope via their parent (part / kit)

Time-series TimescaleDB hypertable

  • inference.inference_event
  • inference.inference_event_image
  • inference.inference_detectobject_predicted
  • inference.inference_runtimestat

All in the inference schema. No company_id. Scope via request_id → job → device → location → company_id. Composite PK includes time_sec (Timescale requirement).

DDL-only tables (no ORM, no API)

The canonical dori_db DDL (alembic/sql/create_dori_schema.sql) creates ~50 tables. Only ~26 are mapped in common/dori_model/dModel.py / dIdoModel.py. The rest exist in Postgres but nothing in this codebase reads or writes them. They're grouped below by domain. Anything you touch here needs a fresh ORM class + Pydantic schema + handler — start from the DDL, not the ORM.

Identity & config

  • sys_config — global system settings
  • d_user_config — per-user preferences (FK app_user)
  • user_log — user activity log (FK app_user)

Company hierarchy

  • d_company_region — regions under a company
  • d_company_sub_region — sub-regions; location FKs here

Camera / recording infra

  • d_vms — Video-Management-System registration
  • d_monitor — per-camera recording config (RTSP creds, codec, section length, VMS FK). Not the monitor service — that's separate.

MLC support

  • d_mlc_post_process — post-processing steps attached to an MLC
  • d_deployment_log — MLC deploy history (FK mlc)

ML platform (training / models)

  • d_dataset — training datasets
  • d_dataset_image — junction (d_dataset × image)
  • d_model — model registry
  • d_model_benchmark — benchmark results (FK d_model)
  • d_project — training projects
  • d_use_case — labelled use case (FK d_model)
  • d_training_benchmarks — training-time benchmarks

Bundle wiring

  • service_bundle_company — which bundles a company may use
  • d_service_bundle_use_case — which use cases a bundle supports

Messaging config

  • d_event_config — per-event-type dispatch rules
  • d_message_template — templated SMS/email bodies
  • d_schedule — cron-like schedule wiring (FK d_event_config)

Incidents & tickets

  • d_incident — job-level incident record (FK job)
  • d_ticket — support ticket (FK subscriber)
  • d_ticket_comment — thread on a ticket

Notes

  • event_note — free-text note on an inference event (linked by string inference_event_id — same soft-link pattern as request_id). Referenced by analytics /event_note endpoint but no ORM class.

Alternate catalog (static kits — separate from dynamic_kit)

  • kit — fixed-composition kit definitions
  • kit_image — junction (kit × image)
  • kit_part — junction (kit × part) with quantity

Inventory

  • d_inventory — stock counts
  • d_inventory_kit — inventory of a static kit
  • d_inventory_location — per-location inventory (FK location)
Why this exists: The DDL is a straight port of the original dori_db schema, which supported an older, larger appserver4 surface. This codebase re-implements a subset — the tables above are stubbed for schema completeness so migrations work, but no handler talks to them yet. When you need one, add: (1) an ORM class in common/dori_model/dModel.py, (2) Pydantic schemas in the relevant pModel.py, (3) handler(s) in the service that owns the domain.

Landmines to know

1. app_user is touched by many endpoints with different scopes and response shapes: /common/user, /infra/user, /ops/employees, /ops/client_user, /dmf/login. All hit the same table.
2. mlc vs mlc_config. mlc is the entity; mlc_config is per-MLC-per-tenant tuning (predictdb creds, NMS thresholds, inference_processes). Historically they shared a handler — the alias has been removed.
3. location denormalizes company_id even though the FK chain goes location → d_company_sub_region → d_company_region → company. Handlers filter on the denormalized column; keep it in sync if you ever add write paths through the region tables.
4. device and job have no company_id. Every device- or job-scoped query joins through location. Miss this join and you leak rows across tenants.
5. Time-series tables have no company_id at all. The inference.* hypertables are scoped via request_id → job → device → location. Analytics endpoints do this join every time — do not query the hypertables raw without it. Also: PKs are composite (id, time_sec) — a plain WHERE id = X won't hit an index.
6. DELETE /infra/company cascades widely. app_user, company_config, d_auth_aws, subscriber*, location (and its devices, and their jobs, pallet_results), part, dynamic_kit, mlc_config all cascade on company_id. Not a soft op.
7. message_log's tenant columns are nullable + denormalized. company_id, location_id, device_id are all NULLABLE. Rely on subscriber_id → subscriber.company_id for authoritative tenant scope.
8. /infra/system_event is a 501 stub. The wired path is /monitor/system_event/. DIM also emits system-event WebSocket frames when NOTIFY triggers fire on message_log.
9. compat.py duplicates a subset of /infra and /ops under legacy paths (/admin/*, /mlc_config, /company/config, …) with camelCase fields and a {status, <key>} envelope for the old ICD frontend served at icd.doriai.com.
10. job.request_id is the join key into the time-series schema, but there's no FK — it's a string. If you rewrite request_id generation, update every analytics query in lockstep.
11. ~24 tables exist in the DDL but have no ORM class or API. Full list under DDL-only tables. Notable examples: d_monitor (per-camera recording config), d_vms, d_model, d_dataset, kit, d_inventory*, d_ticket*, d_incident, d_message_template. Before assuming a table isn't used, check alembic/sql/create_dori_schema.sql — it's the schema of record. Before assuming a table is used, grep common/dori_model/ and services/. Silent gap.