Sense — Data Model¶

Sense persists state in three places, each chosen for a specific shape of data.

Frame bytes never live in either DB — they're in S3. Postgres holds the lifecycle row; ClickHouse holds the searchable metadata.

Postgres ER (main entities)¶

erDiagram
  USERS ||--o{ USER_ORG : "is in"
  ORGANIZATIONS ||--o{ USER_ORG : "has"
  USER_ORG ||--o{ USER_ORG_ROLE : "grants"
  ROLES ||--o{ USER_ORG_ROLE : "is"

  ORGANIZATIONS ||--o{ TRACK_LOGS : "scopes"
  TRACK_LOGS ||--o{ FRAME_BATCHES : "has"

  ORGANIZATIONS ||--o{ HOLDS : "owns"
  ORGANIZATIONS ||--o{ DETOUR_TASKS : "owns"
  PROJECTS ||--o{ DETOUR_TASKS : "groups"
  TERRITORIES ||--o{ DETOUR_TASKS : "scopes"

  ORGANIZATIONS ||--o{ CUSTOM_LAYERS : "owns"
  ORGANIZATIONS ||--o{ WMS_SOURCES : "owns"
  CUSTOM_LAYERS ||--o| PRIORITY_LAYERS : "is sometimes"

  TRACK_LOGS ||--o| CITYLENS_TRACK_MAPPING : "may map to"

  USERS {
    bigint id PK "IDENTITY"
    string external_auth_id UK
    string email
    string display_name
    bool is_active
    jsonb profile_data "indexed JSONB filters"
  }
  ORGANIZATIONS {
    bigint id PK
    string name
    string code UK
  }
  USER_ORG {
    bigint user_id FK
    bigint organization_id FK
    timestamptz expires_at
  }
  ROLES {
    bigint id PK
    string name
    bigint inherits_from_id FK
  }
  USER_ORG_ROLE {
    bigint user_id FK
    bigint organization_id FK
    bigint role_id FK
  }
  TRACK_LOGS {
    uuid id PK "UUIDv7 = track_id"
    bigint organization_id FK
    bigint user_id FK
    timestamptz created_at "PARTITION KEY (monthly)"
    string s3_log_key "log/{id}"
  }
  FRAME_BATCHES {
    uuid id PK
    uuid track_id FK
    string status "pending | committed"
    timestamptz commit_time
    timestamptz created_at "PARTITION KEY (monthly)"
  }
  HOLDS {
    bigint id PK
    bigint organization_id FK
    geometry location "PointZ, SRID 4326"
    text message
    timestamptz created_at
  }
  DETOUR_TASKS {
    bigint id PK
    bigint organization_id FK
    bigint project_id FK
    bigint territory_id FK
    string status "AVAILABLE..VERIFIED"
    int priority
    geometry geometry "WKB"
    bigint assigned_driver_id FK
    int percent_completed
    timestamptz deadline
  }
  CUSTOM_LAYERS {
    bigint id PK
    bigint organization_id FK
    string name
    string s3_pmtiles_key
    timestamptz updated_at
  }
  CITYLENS_TRACK_MAPPING {
    string citylens_id PK
    uuid axion_id UK
    timestamptz created_at
  }

Some columns shown in the diagram are summarized — the canonical schema is in axion.sense.backend/src/Axion.Sense.Data/Entities/.

Partitioning (pg_partman)¶

PartmanMaintenanceJob runs nightly: creates next month's partition, drops anything past retention. Custom PartitionedMigrationsSqlGenerator in EF makes new tables PARTITION BY RANGE aware so future pg_partman setup is one-line.

ClickHouse schema¶

Skip indexes (bloom filters):

• frames: on h3_index_res9, h3_index_res12, user_id
• detections: on classes Array(String)

Why plain ReplicatedMergeTree + client-side DELETE + INSERT — at-least-once delivery from Kafka and from the Citylens importer means duplicates can sneak in. We use writer-side DELETE by primary key (with mutations_sync = 1, allow_nondeterministic_mutations = 1) followed by INSERT so each row is replaced cleanly. ReplacingMergeTree was the earlier choice but the required SELECT … FINAL cost killed read latency under load; plain ReplicatedMergeTree keeps reads fast at the cost of a heavier write path.

Why UUIDv7ToDateTime — track and frame IDs are UUIDv7, so the timestamp is encoded in the ID. Partitioning by toYYYYMM(UUIDv7ToDateTime(id)) gives us time-ordered partitions with no extra timestamp column to maintain.

Hangfire schema (axion_sense_tasks)¶

Standard Hangfire.PostgreSql 1.21 schema: HangfireJob, HangfireJobParameter, HangfireState, HangfireCounter, HangfireHash, HangfireSet, HangfireList, HangfireServer. Plus the worker-only citylens_migration_state row.

Migrations¶