Skip to content

PostgreSQL

The transactional store for the entire platform.

At a glance

Property Value
Version PostgreSQL 17
Extensions PostGIS, pg_partman, OpenFGA's required extensions
Deployed Externally (cloud-managed or customer infra) — not in axion.infra
Port 5432
Connection pooling EF Core's built-in pool; PgBouncer optional in front
Backup Provider-side; verify nightly snapshot + WAL archive
High availability Provider-side primary + read replica

Databases

The platform uses four logical databases on the same cluster:

Database Owner What's in it
axion_sense Sense API + Worker Domain state — users, orgs, roles, tracks (logs), frame batches, holds, detour tasks, custom layers, Citylens mappings, OpenFGA model versions
axion_sense_tasks Sense Worker only Hangfire job state + worker-only tables
axion_sense_permissions OpenFGA Relationship tuples and authorization model store — see OpenFGA
axion_gen Gen API Dashboards, widgets, data sources (encrypted credentials), board layouts

Schemas in axion_sense

Schema Contents
public Users, organizations, roles, OpenFGA model versions, reference items
road_data Track logs, frame batches, holds — partitioned monthly via pg_partman
planner Detour tasks, territories, projects, custom layers, WMS sources, priority layers
external Citylens mappings; foreign integrations

See Sense Data Model for the ER and full table list.

PostGIS usage

  • geometry(PointZ, 4326) columns for hold locations and similar pin-style data.
  • geometry(LineString, 4326) and geometry(Polygon, 4326) for territories and detour task geometries.
  • GiST indexes on geometry columns for spatial queries (ST_Contains, ST_DWithin).

Partitioning (pg_partman)

Table Partition by Granularity Retention
road_data.track_logs created_at Monthly None
road_data.frame_batches created_at Monthly None
Outbox-style tables created_at Daily 7 days

Maintenance runs nightly via the Worker's PartmanMaintenanceJob (CALL run_maintenance()). The job creates upcoming partitions and drops aged-out ones.

EF Core migrations are partition-aware via a custom PartitionedMigrationsSqlGenerator that appends PARTITION BY RANGE (column) to CREATE TABLE for partman-managed tables.

Migrations

Three migration runners — one per database:

# Sense API: axion_sense + ClickHouse + OpenFGA model
dotnet Axion.Sense.Api.dll --migrate

# Sense Worker: axion_sense_tasks + Kafka topics
dotnet Axion.Sense.Worker.dll --migrate

# Gen API: axion_gen
dotnet Axion.Gen.Api.dll --migrate

These run as one-shot Kubernetes jobs via Helm pre-install / pre-upgrade hooks. They exit with non-zero on failure, blocking the rollout.

Roles & grants

A typical setup:

Role Grants
axion_sense_app SELECT, INSERT, UPDATE, DELETE on schemas public, road_data, planner, external; no DDL
axion_sense_migrate CREATE, ALTER — used only by the migration job
axion_sense_worker Same as axion_sense_app plus access to axion_sense_tasks
axion_gen_app Same shape, scoped to axion_gen

Migration runners use the _migrate role; the API + Worker use the _app / _worker role. Separating them means a runaway query in the app process can't accidentally DROP TABLE.

Backup & restore

  • Provider-side automated snapshots + WAL archiving — verify with the provider's recovery test cadence.
  • Logical dumps (pg_dump) optional for cross-cluster restore (e.g., staging refresh from prod).
  • PITR (point-in-time recovery): provider-side; document the RPO target with the operations team.

Observability

  • Postgres exporter scraped by SigNoz (latencies, locks, connection counts).
  • EF Core query logs at Information level (no parameters by default — PII safety); Debug includes parameters and is for dev only.
  • Slow query logging (log_min_duration_statement) configured to 250ms.

Capacity hints

(Mark TBD where unverified — fill in from production load tests.)

Resource Sense Gen
Storage growth TBD GiB / month TBD GiB / month
Connection count (steady) API: ~30, Worker: ~10 API: ~10
Peak QPS TBD TBD
RTT API → DB ≤ 5ms p95 (same VPC) same

Common operational tasks

  • Schema rename or column drop: write a forward migration; ship; later (next release) write a separate cleanup migration. Don't combine.
  • Adding a partition retention policy: extend PartmanMaintenanceJob config; partman handles rest.
  • Investigating slow queries: SigNoz dashboard Postgres / Slow queries; correlate by request_id with API trace.