Database Layout

✓

Documentation Verified Last checked: 2025-11-25 Reviewer: Christof Buchbender

This page shows the complete database schema diagram. The diagram is auto-generated from SQLAlchemy models and shows all tables and their relationships.

How to Read the Diagram

• Boxes represent database tables

• Lines represent foreign key relationships

• Arrows point from child table (with foreign key) to parent table (referenced)

• Polymorphic inheritance is shown with “type” discriminator columns

• The diagram is comprehensive but complex - the concept pages provide explanations of subsystems

Schema Diagram

Note

Rendering requires sphinxcontrib-mermaid to be registered in the docs build’s conf.py (extensions = [..., "sphinxcontrib.mermaid"]).

        erDiagram
    api_token {
        int id PK
        int user_id FK
        string name
        string token_hash
        string token_prefix
        json scopes
        datetime created_at
        datetime expires_at
        datetime last_used_at
        bool active
        int usage_count
        string last_used_ip
    }

    chai_inpar_parameter {
        int id PK
        string name
        int version
        json history
        float lam
        float bet
        string cormap
        string line_range
        float goal_resolution
        string refname
        float refoffl
        float refoffb
        string corref
        string mode
        string otfpattern
        float ton
        float toff
        int repetition
        int offononoff
        float stepl
        float stepb
        float mapsizel
        float mapsizeb
        int nmapl
        int nmapb
        float mapangle
        float crosssizel
        float crosssizeb
        int reverseflg
        int scan_dir
        int scan_order
        int evendump
        int novertical
        int pointingflg
        int offperload
        int onperload
        int repperload
        int lineperoff
        int onperoff
        int offperpattern
        string refpoint
        int act_pixflg
        string nextflg
    }

    chai_module_configuration {
        int id PK FK
        int line_id FK
        json config_parameters
    }

    chai_observation_configuration {
        int id PK FK
        int ntilelines
    }

    chai_tiling {
        int id PK
        int version
        json history
        int priority_in_tiling
        string tile_id
        float tile_offset_x
        float tile_offset_y
        string x_or_y
        float tile_unit_scaling_x
        float tile_unit_scaling_y
        string edge
        int goal_ncycle
        int chai_observation_configuration_id FK
        int chai_inpar_parameter_id FK
    }

    constant_elevation_source {
        int id PK FK
        float ra_deg_min
        float ra_deg_max
        float dec_deg_min
        float dec_deg_max
        string slam_min
        string slam_max
        string sbet_min
        string sbet_max
        float vlsr
        string frame
    }

    data_archive {
        int id PK
        string name
    }

    data_location {
        int id PK
        string name
        string location_type
        int site_id FK
        bool active
        int priority
        string storage_type
    }

    data_transfer {
        int id PK
        string process_id
        int retry_count
        datetime start_time
        datetime end_time
        string data_transfer_method
        string status
        text failure_error_message
        text transfer_program_log
        int origin_location_id FK
        int destination_location_id FK
        int data_transfer_package_id FK
        string unpack_status
        text unpack_failure_error_message
        datetime unpack_start_time
        datetime unpack_end_time
        text unpack_log
        int unpack_retry_count
    }

    data_transfer_log {
        int id PK FK
        int data_transfer_id FK
        datetime timestamp
        string status
        string log_path
    }

    data_transfer_package {
        int id PK
        string hash_id
        string file_name
        bigint size
        string checksum
        string relative_path
        int origin_location_id FK
        string status
        text failure_error_message
        int retry_count
    }

    data_transfer_package_files {
        int data_transfer_package_id PK FK
        uuid file_id PK FK
    }

    data_transfer_package_physical_copy {
        int id PK FK
        int data_transfer_package_id FK
    }

    data_transfer_route {
        int id PK
        string name
        int origin_site_id FK
        int destination_site_id FK
        string route_type
        string transfer_method
        int origin_location_id FK
        int destination_location_id FK
        int relay_site_id FK
    }

    disk_data_location {
        int id PK FK
        string path
        string host
        string user
    }

    executed_obs_unit {
        uuid id PK
        datetime start_time
        datetime end_time
        string status
        float mean_pwv
        float mean_elevation
        int obs_unit_id FK
        json obs_info
        json obs_progress
    }

    fixed_source {
        int id PK FK
        float ra_deg
        float dec_deg
        string slam
        string sbet
        float vlsr
        string frame
    }

    instrument {
        int id PK
        string name
        string instrument_type
        string description
        int telescope_id FK
        bool available
    }

    instrument_module {
        int id PK
        string name
        string description
        int pixels
        int instrument_id FK
        bool available
    }

    instrument_module_configuration {
        int id PK
        string type
        int instrument_module_id FK
    }

    instrument_observing_program {
        int instrument_id PK FK
        int observing_program_id PK FK
    }

    line {
        int id PK
        string name
        float rest_frequency
        string side_band
        bool available
        string comment
    }

    long_term_archive_transfer {
        int id PK
        int site_id FK
        int origin_data_location_id FK
        int destination_data_location_id FK
        int raw_data_package_id FK
        string status
        text failure_error_message
        datetime start_time
        datetime end_time
        int attempt_count
        datetime last_attempt_time
        text error_message
    }

    long_term_archive_transfer_log {
        int id PK FK
        int long_term_archive_transfer_id FK
        text log
        datetime timestamp
    }

    obs_mode {
        int id PK
        string name
        string description
    }

    obs_unit {
        int id PK
        string name
        int version
        json history
        string phase
        string group
        string group_type
        float equal_tolerance
        float min_alt
        float max_alt
        float min_rotang
        float max_rotang
        string nominal_alt
        float min_lsa
        float max_lsa
        float lsa_margin
        float cadence
        float requested_time_h
        float unit_duration_h
        float trans_ref
        float priorities
        bool available
        bool pre_scheduled_basis
        json additional_parameters
        int source_id FK
        int observing_program_id FK
        int sub_observing_program_id FK
        int obs_mode_id FK
        int primary_instrument_module_configuration_id FK
        int observation_configuration_id FK
    }

    obs_unit_instrument_module_configuration_association {
        int obs_unit_id FK
        int instrument_module_configuration_id FK
    }

    observation_configuration {
        int id PK
        string type
        json azimuth_range
    }

    observatory {
        int id PK
        string name
        string description
    }

    observing_program {
        int id PK
        string name
        string short_name
        text description
        int lead_id FK
    }

    physical_copy {
        int id PK
        string type
        int data_location_id FK
        datetime created_at
        datetime verified_at
        string status
        string checksum
        int deletion_task_id
        datetime deleted_at
    }

    pre_scheduled_slot {
        int id PK
        datetime start_time
        datetime end_time
        int obs_unit_id FK
    }

    prime_cam_module_configuration {
        int id PK FK
        json config_parameters
    }

    prime_cam_observation_configuration {
        int id PK FK
        int version
        json history
        json mapping_parameters
    }

    raw_data_file {
        uuid id PK
        string name
        string relative_path
        datetime created_at
        int instrument_module_configuration_id FK
        string file_type
        bigint size
        string checksum
        text description
        int source_location_id FK
        int raw_data_package_id FK
        int data_transfer_package_id FK
        uuid executed_obs_unit_id FK
        string state
    }

    raw_data_file_physical_copy {
        int id PK FK
        uuid raw_data_file_id FK
    }

    raw_data_package {
        int id PK
        string name
        string relative_path
        bigint size
        uuid executed_obs_unit_id FK
        int instrument_module_id FK
        int obs_unit_id FK
        datetime created_at
        string checksum
        int data_transfer_package_id FK
        string analyze_status
        string status
        string state
        int retry_count
    }

    raw_data_package_metadata {
        int id PK
        int raw_data_package_id FK
        datetime created_at
        datetime updated_at
        json instrument_specific
        json quality_metrics
        json provenance
        json custom_metadata
    }

    raw_data_package_physical_copy {
        int id PK FK
        int raw_data_package_id FK
    }

    raw_data_package_staging_job_association {
        int raw_data_package_id FK
        int staging_job_id FK
    }

    role {
        int id PK
        string name
        string description
        json github_team_mappings
        json permissions
    }

    s3_data_location {
        int id PK FK
        string bucket_name
        string region
        string endpoint_url
    }

    site {
        int id PK
        string name
        string short_name
        string site_location
    }

    solar_system_object {
        int id PK FK
        string eph_name
        string naif_id
    }

    source {
        int id PK
        string name
        string type
    }

    staging_job {
        int id PK
        string status
        text failure_error_message
        datetime start_time
        bool active
        datetime end_time
        int retry_count
        int origin_data_location_id FK
        int destination_data_location_id FK
    }

    staging_job_log {
        int id PK FK
        int staging_job_id FK
        text log
        datetime timestamp
    }

    sub_observing_program {
        int id PK
        string name
        string short_name
        text description
        int observing_program_id FK
    }

    system_log {
        int id PK
        string type
    }

    tape_data_location {
        int id PK FK
        string library_name
        string mount_path
    }

    telescope {
        int id PK
        string name
        string description
        float lon_deg
        float lat_deg
        float alt_m
        int observatory_id FK
    }

    user {
        int id PK
        string email
        string username
        string first_name
        string last_name
        string title
        text affiliation
        string password
        string github_id
        string github_username
        json preferences
        datetime last_login_at
        datetime current_login_at
        string last_login_ip
        string current_login_ip
        int login_count
        bool active
        datetime confirmed_at
    }

    user_role_association {
        int user_id PK FK
        int role_id PK FK
    }

    user ||--|{ api_token : "user_id"
    instrument_module_configuration ||--|{ chai_module_configuration : "id"
    line ||--|{ chai_module_configuration : "line_id"
    observation_configuration ||--|{ chai_observation_configuration : "id"
    chai_observation_configuration ||--o{ chai_tiling : "chai_observation_configuration_id"
    chai_inpar_parameter ||--o{ chai_tiling : "chai_inpar_parameter_id"
    source ||--|{ constant_elevation_source : "id"
    site ||--|{ data_location : "site_id"
    data_location ||--|{ data_transfer : "origin_location_id"
    data_location ||--|{ data_transfer : "destination_location_id"
    data_transfer_package ||--|{ data_transfer : "data_transfer_package_id"
    system_log ||--|{ data_transfer_log : "id"
    data_transfer ||--|{ data_transfer_log : "data_transfer_id"
    data_location ||--|{ data_transfer_package : "origin_location_id"
    data_transfer_package ||--|{ data_transfer_package_files : "data_transfer_package_id"
    raw_data_file ||--|{ data_transfer_package_files : "file_id"
    physical_copy ||--|{ data_transfer_package_physical_copy : "id"
    data_transfer_package ||--|{ data_transfer_package_physical_copy : "data_transfer_package_id"
    site ||--|{ data_transfer_route : "origin_site_id"
    site ||--|{ data_transfer_route : "destination_site_id"
    data_location ||--o{ data_transfer_route : "origin_location_id"
    data_location ||--o{ data_transfer_route : "destination_location_id"
    site ||--o{ data_transfer_route : "relay_site_id"
    data_location ||--|{ disk_data_location : "id"
    obs_unit ||--|{ executed_obs_unit : "obs_unit_id"
    source ||--|{ fixed_source : "id"
    telescope ||--o{ instrument : "telescope_id"
    instrument ||--o{ instrument_module : "instrument_id"
    instrument_module ||--o{ instrument_module_configuration : "instrument_module_id"
    instrument ||--|{ instrument_observing_program : "instrument_id"
    observing_program ||--|{ instrument_observing_program : "observing_program_id"
    site ||--o{ long_term_archive_transfer : "site_id"
    data_location ||--o{ long_term_archive_transfer : "origin_data_location_id"
    data_location ||--o{ long_term_archive_transfer : "destination_data_location_id"
    raw_data_package ||--o{ long_term_archive_transfer : "raw_data_package_id"
    system_log ||--|{ long_term_archive_transfer_log : "id"
    long_term_archive_transfer ||--|{ long_term_archive_transfer_log : "long_term_archive_transfer_id"
    source ||--o{ obs_unit : "source_id"
    observing_program ||--|{ obs_unit : "observing_program_id"
    sub_observing_program ||--o{ obs_unit : "sub_observing_program_id"
    obs_mode ||--o{ obs_unit : "obs_mode_id"
    instrument_module_configuration ||--|{ obs_unit : "primary_instrument_module_configuration_id"
    observation_configuration ||--o{ obs_unit : "observation_configuration_id"
    obs_unit ||--o{ obs_unit_instrument_module_configuration_association : "obs_unit_id"
    instrument_module_configuration ||--o{ obs_unit_instrument_module_configuration_association : "instrument_module_configuration_id"
    user ||--o{ observing_program : "lead_id"
    data_location ||--|{ physical_copy : "data_location_id"
    obs_unit ||--|{ pre_scheduled_slot : "obs_unit_id"
    instrument_module_configuration ||--|{ prime_cam_module_configuration : "id"
    observation_configuration ||--|{ prime_cam_observation_configuration : "id"
    instrument_module_configuration ||--o{ raw_data_file : "instrument_module_configuration_id"
    data_location ||--|{ raw_data_file : "source_location_id"
    raw_data_package ||--o{ raw_data_file : "raw_data_package_id"
    data_transfer_package ||--o{ raw_data_file : "data_transfer_package_id"
    executed_obs_unit ||--|{ raw_data_file : "executed_obs_unit_id"
    physical_copy ||--|{ raw_data_file_physical_copy : "id"
    raw_data_file ||--|{ raw_data_file_physical_copy : "raw_data_file_id"
    executed_obs_unit ||--|{ raw_data_package : "executed_obs_unit_id"
    instrument_module ||--|{ raw_data_package : "instrument_module_id"
    obs_unit ||--|{ raw_data_package : "obs_unit_id"
    data_transfer_package ||--o{ raw_data_package : "data_transfer_package_id"
    raw_data_package ||--|{ raw_data_package_metadata : "raw_data_package_id"
    physical_copy ||--|{ raw_data_package_physical_copy : "id"
    raw_data_package ||--|{ raw_data_package_physical_copy : "raw_data_package_id"
    raw_data_package ||--o{ raw_data_package_staging_job_association : "raw_data_package_id"
    staging_job ||--o{ raw_data_package_staging_job_association : "staging_job_id"
    data_location ||--|{ s3_data_location : "id"
    source ||--|{ solar_system_object : "id"
    data_location ||--|{ staging_job : "origin_data_location_id"
    data_location ||--|{ staging_job : "destination_data_location_id"
    system_log ||--|{ staging_job_log : "id"
    staging_job ||--|{ staging_job_log : "staging_job_id"
    observing_program ||--|{ sub_observing_program : "observing_program_id"
    data_location ||--|{ tape_data_location : "id"
    observatory ||--o{ telescope : "observatory_id"
    user ||--|{ user_role_association : "user_id"
    role ||--|{ user_role_association : "role_id"

    

CCAT Operations Database Schema

Navigation Help

For conceptual understanding, see the Overview.

For detailed table documentation, see Table Reference.

Links to major concept pages:

• Observatory infrastructure: Observatory Hierarchy

• Observation planning and execution: Observation Model

• Data files and packages: Data Model

• Sites and locations: Location Model

• Transfer infrastructure: Transfer Model

Major Table Groups

The database is organized into several major groups:

Observatory

Observatory, Telescope, Instrument, InstrumentModule

Programs

ObservingProgram, SubObservingProgram, ObsUnit, ExecutedObsUnit

Sources

Source, FixedSource, SolarSystemObject, ConstantElevationSource

Data

RawDataFile, RawDataPackage, DataTransferPackage

Locations

Site, DataLocation, DiskDataLocation, S3DataLocation, TapeDataLocation

Transfers

DataTransferRoute, DataTransfer, LongTermArchiveTransfer, StagingJob

Physical Copies

PhysicalCopy, RawDataFilePhysicalCopy, RawDataPackagePhysicalCopy, DataTransferPackagePhysicalCopy

Configuration

ObservationConfiguration, InstrumentModuleConfiguration

Access

User, Role, ApiToken

How the Diagram is Generated

The file docs/source/schema/schema.mmd is generated by scripts/generate_mermaid_schema.py using only SQLAlchemy (no system dependencies). Re-run it whenever models.py changes and commit the updated .mmd file alongside the model changes:

python scripts/generate_mermaid_schema.py
git add docs/source/schema/schema.mmd
git commit -m "update schema diagram"

Related Documentation

• Complete table reference: Table Reference

• Complete API reference: Models API Reference

• Concept overview: Overview