Database Schema

Purpose

This document records the initial PostgreSQL schema boundary for the TRAQ server. The goal is to move metadata and workflow state into PostgreSQL while keeping binary artifacts on disk.

Design rules

  • PostgreSQL is the intended runtime target.

  • SQLAlchemy models define the application schema.

  • Filesystem storage remains in place for:

    • uploaded audio

    • uploaded images

    • generated PDFs

    • generated DOCX

    • exported GeoJSON

  • Working round data may be pruned after archive if final snapshots and audit history are preserved.

Model modules

  • app/db.py: engine/session bootstrap, declarative base, and the local schema creation helper.

  • app/db_models.py: ORM tables for devices, jobs, rounds, media metadata, finals, artifacts, and events.

Core tables

Permanent records:

  • devices

  • device_tokens

  • customers

  • billing_profiles

  • operators

  • trees

  • jobs

  • job_assignments

  • job_finals

  • job_geojson_exports

  • artifacts

  • job_events

Working records:

  • job_rounds

  • round_recordings

  • round_images

Intent by table

devices

Registered devices and their approval/role state.

device_tokens

Issued bearer tokens for approved devices.

jobs

Top-level job record, job number, current status, and archived final snapshots. Jobs reference reusable customer, billing, and operator rows while keeping job-specific work fields locally on the job row.

customers

Reusable customer/contact identity imported from job_record.json.

billing_profiles

Reusable billing/contact identity imported from job_record.json.

operators

Reusable assessor/operator identity derived from archived final provenance.

trees

Reusable customer-scoped tree identities. A tree number is unique within a customer and may be referenced by multiple jobs over time.

job_assignments

Current one-device-at-a-time assignment mapping.

job_rounds

Working review rounds and cached review payload/manifests.

round_recordings / round_images

Uploaded media metadata and artifact paths.

job_finals

Final or correction snapshots retained after working rounds may be pruned.

job_geojson_exports

Stored GeoJSON export objects linked to the job and export kind. The raw GeoJSON payload is kept in PostgreSQL as JSONB while file exports remain on disk.

artifacts

File references for generated and uploaded non-database artifacts.

job_events

Append-only audit trail for status, assignment, and finalization events.

Operational direction

Migration order should be:

  1. device auth and token state

  2. jobs and assignments

  3. round metadata and manifests

  4. media metadata

  5. final/correction metadata

  6. audit trail

The runtime storage migration is complete for operational state. The filesystem remains for artifact bytes and exported debug/compatibility copies.

See also:

  • docs/runtime_export_boundary.rst