Database Layout
===============

.. verified:: 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"]``).

.. mermaid:: schema.mmd
   :caption: CCAT Operations Database Schema

Navigation Help
---------------

For conceptual understanding, see the :doc:`../concepts/overview`.

For detailed table documentation, see :doc:`table_reference`.

Links to major concept pages:

* **Observatory infrastructure**: :doc:`../concepts/observatory_hierarchy`
* **Observation planning and execution**: :doc:`../concepts/observation_model`
* **Data files and packages**: :doc:`../concepts/data_model`
* **Sites and locations**: :doc:`../concepts/location_model`
* **Transfer infrastructure**: :doc:`../concepts/transfer_model`

Major Table Groups
------------------

The database is organized into several major groups:

**Observatory**
   :py:class:`~ccat_ops_db.models.Observatory`,
   :py:class:`~ccat_ops_db.models.Telescope`,
   :py:class:`~ccat_ops_db.models.Instrument`,
   :py:class:`~ccat_ops_db.models.InstrumentModule`

**Programs**
   :py:class:`~ccat_ops_db.models.ObservingProgram`,
   :py:class:`~ccat_ops_db.models.SubObservingProgram`,
   :py:class:`~ccat_ops_db.models.ObsUnit`,
   :py:class:`~ccat_ops_db.models.ExecutedObsUnit`

**Sources**
   :py:class:`~ccat_ops_db.models.Source`,
   :py:class:`~ccat_ops_db.models.FixedSource`,
   :py:class:`~ccat_ops_db.models.SolarSystemObject`,
   :py:class:`~ccat_ops_db.models.ConstantElevationSource`

**Data**
   :py:class:`~ccat_ops_db.models.RawDataFile`,
   :py:class:`~ccat_ops_db.models.RawDataPackage`,
   :py:class:`~ccat_ops_db.models.DataTransferPackage`

**Locations**
   :py:class:`~ccat_ops_db.models.Site`,
   :py:class:`~ccat_ops_db.models.DataLocation`,
   :py:class:`~ccat_ops_db.models.DiskDataLocation`,
   :py:class:`~ccat_ops_db.models.S3DataLocation`,
   :py:class:`~ccat_ops_db.models.TapeDataLocation`

**Transfers**
   :py:class:`~ccat_ops_db.models.DataTransferRoute`,
   :py:class:`~ccat_ops_db.models.DataTransfer`,
   :py:class:`~ccat_ops_db.models.LongTermArchiveTransfer`,
   :py:class:`~ccat_ops_db.models.StagingJob`

**Physical Copies**
   :py:class:`~ccat_ops_db.models.PhysicalCopy`,
   :py:class:`~ccat_ops_db.models.RawDataFilePhysicalCopy`,
   :py:class:`~ccat_ops_db.models.RawDataPackagePhysicalCopy`,
   :py:class:`~ccat_ops_db.models.DataTransferPackagePhysicalCopy`

**Configuration**
   :py:class:`~ccat_ops_db.models.ObservationConfiguration`,
   :py:class:`~ccat_ops_db.models.InstrumentModuleConfiguration`

**Access**
   :py:class:`~ccat_ops_db.models.User`,
   :py:class:`~ccat_ops_db.models.Role`,
   :py:class:`~ccat_ops_db.models.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:

.. code-block:: bash

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

Related Documentation
---------------------

* Complete table reference: :doc:`table_reference`
* Complete API reference: :doc:`../api_reference/models`
* Concept overview: :doc:`../concepts/overview`