# Database Layout

```{eval-rst}
.. 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"]`).
:::

```{eval-rst}
.. 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:

```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`