Skip to content

03. Data Model

The data model is the foundation of AClimate v3. The aclimate_v3_orm package is the authoritative definition containing all SQLAlchemy models, Pydantic schemas, Alembic migrations, services, and validations.

Role of the ORM as Source of Truth

The aclimate_v3_orm package defines:

  • Models (31 entities): SQLAlchemy entity definitions with columns, types, constraints, and relationships
  • Schemas (31): Pydantic serialization/deserialization contracts for the API
  • Services (31): Business logic for CRUD operations
  • Validations (31): Integrity rules and constraints
  • Enums (7): Shared enumerations
  • Migrations: Alembic version-controlled schema evolution

Database

PostgreSQL is the primary data store. The schema is managed entirely through SQLAlchemy ORM models with Alembic migrations for version control.

Data Model Conventions

Convention Description
Naming Snake_case for tables and columns (PostgreSQL convention)
Primary Keys Integer auto-increment (id column) for most tables; BigInteger for high-volume tables
Timestamps Every entity includes register (created_at) and updated (updated_at) with timezone support
Soft Delete Entities are disabled via an enable boolean column (not physically deleted)
Foreign Keys Explicit ForeignKey constraints with indexed columns
Relationships Defined with SQLAlchemy relationship() for ORM navigation

Schema Domains

The ORM entities are organized into functional domains:

Prefix Domain Count Description
mng_ Management 20 Core configuration entities (countries, crops, soils, indicators)
forecast_ Forecast 2 Forecast runs and analogue data
climate_historical_ Climate 4 Historical climate observations and derived data
historical_agroclimatic_ Agroclimatic 1 Processed agroclimatic indicators
phenological_stage_ Phenology 1 Crop phenological stages and stress data
user_ / users Security 3 Users, roles, and access control

Sections