Repository Restructuring Proposal

[lsetiawan]

Summary

Restructure the ca-biositing repository to support a full-stack web application architecture with three main components:

1. Backend API - REST API for data access and business logic
2. ETL Pipeline - Data ingestion from Google Sheets and other sources
3. Frontend - Web interface (as a Git submodule from separate repository)

Motivation

The current repository structure is primarily organized around the ETL pipeline (etl_merge/my_etl_project/), with limited organization for a full-stack web application. Though it looks like @petercarbsmith is working on separating these out as I write this proposal (See #46).

As we expand to include a backend API and frontend interface, we need a clear separation of concerns that:

• Enables independent development and deployment of each component
• Shares common code (database models) between API and ETL
• Maintains clear boundaries and responsibilities
• Supports scalable team collaboration
• Facilitates testing at unit, component, and integration levels

Proposed Structure

ca-biositing/                           # Main repository root
├── README.md                           # Main project documentation
├── CONTRIBUTING.md                     # Contribution guidelines
├── LICENSE
├── AGENTS.md
├── .gitignore
├── .gitmodules                         # NEW: Git submodules configuration
├── pixi.toml
├── pyproject.toml
├── docker-compose.yml                  # NEW: Root-level orchestration
│
├── docs/                              # NEW: Comprehensive documentation
│   ├── architecture.md
│   ├── api/
│   ├── etl/
│   └── deployment.md
│
├── frontend/                          # NEW: Frontend submodule (separate repo)
│   └── .git -> [submodule reference]
│
├── backend/                           # NEW: Backend API application
│   ├── README.md
│   ├── Dockerfile
│   ├── requirements.txt
│   ├── alembic.ini
│   ├── config.py
│   ├── main.py
│   ├── .env.example
│   ├── alembic/
│   │   └── versions/
│   ├── src/
│   │   ├── api/                       # API routes and endpoints
│   │   │   └── v1/
│   │   │       └── endpoints/
│   │   ├── schemas/                   # Pydantic schemas
│   │   ├── services/                  # Business logic
│   │   ├── database.py
│   │   ├── auth.py
│   │   └── utils/
│   └── tests/
│
├── etl/                               # MOVED: From etl_merge/my_etl_project/
│   ├── README.md
│   ├── Dockerfile
│   ├── requirements.txt
│   ├── config.py
│   ├── run_pipeline.py
│   ├── .env.example
│   ├── src/
│   │   ├── extract/
│   │   ├── transform/
│   │   ├── load/
│   │   ├── utils/
│   │   └── templates/
│   └── tests/
│
├── shared/                            # NEW: Shared code (models, DB, utils)
│   ├── models/                        # Database models (SQLModel)
│   │   ├── biomass.py
│   │   ├── experiments_analysis.py
│   │   ├── geographic_locations.py
│   │   └── ...
│   ├── database.py
│   └── utils/
│
├── scripts/                           # NEW: Development/deployment scripts
│   ├── setup_dev.sh
│   ├── migrate_db.sh
│   └── deploy.sh
│
└── tests/                             # Integration tests
    └── test_integration/

NOTE: The structure above is merely a suggestion and not the exact files/folders that needs to be created.

Key Design Decisions

Shared Models Strategy

Both the backend API and ETL pipeline need access to the same database models. Options considered:

1. ✅ Shared directory (Recommended): Create shared/models/ imported by both
2. ❌ Duplicate models: Leads to inconsistencies
3. ❌ API depends on ETL: Creates unnecessary coupling

Decision: Use shared/ directory for database models, database connection logic, and common utilities.

Frontend Repository Strategy

Options considered:

1. ✅ Git submodule (Recommended): Maintains separate repo, allows independent deployment
2. ❌ Monorepo: Increases repository size, couples release cycles
3. ❌ Separate repos with no link: Harder to coordinate versions

Decision: Use Git submodule for frontend, enabling independent development while maintaining version coordination.

Docker Orchestration

• Root-level docker-compose.yml orchestrates all services
• Each component has its own Dockerfile for independence
• Shared database service for both API and ETL
• Separate compose files for development vs production

Environment Management

• Pixi continues to manage local development tools (pre-commit, pytest, QGIS)
• Docker manages runtime environments (PostgreSQL, API, ETL)
• Each component has .env.example for required variables
• Shared database connection via environment variables

Migration Risks & Mitigations

Risk	Impact	Mitigation
Breaking existing ETL pipelines	High	Incremental migration with testing at each step
Import path changes	Medium	Create migration script to update all imports
Team coordination	Medium	Clear communication, migration guide, pair programming
CI/CD pipeline breaks	High	Update CI/CD configs before merging
Lost git history	Low	Use git mv to preserve history
Docker configuration errors	Medium	Test thoroughly in development environment

Benefits

• ✅ Modular Architecture: Clear separation between API, ETL, and frontend
• ✅ Scalability: Easy to add new services or split components further
• ✅ Developer Experience: Clear boundaries reduce cognitive load
• ✅ DRY Principle: Shared code prevents duplication
• ✅ Independent Development: Teams can work on different components simultaneously
• ✅ Flexible Deployment: Deploy components independently or together
• ✅ Better Testing: Enables focused unit and integration tests
• ✅ Version Control: Submodule allows frontend versioning

Success Criteria

• All existing ETL pipelines run without modification to business logic
• Backend API serves all required endpoints with documentation
• Frontend successfully connects to API
• Full stack runs with single docker-compose up command
• All tests pass (unit, integration, end-to-end)
• Documentation is complete and accurate
• Team members can set up development environment in < 15 minutes
• CI/CD pipeline successfully builds and tests all components

References

• Current structure: etl_merge/my_etl_project/
• SSEC Best Practices: https://rse-guidelines.readthedocs.io/en/latest/
• Docker Compose Documentation: https://docs.docker.com/compose/
• Git Submodules: https://git-scm.com/book/en/v2/Git-Tools-Submodules
• FastAPI Documentation: https://fastapi.tiangolo.com/
• SQLModel Documentation: https://sqlmodel.tiangolo.com/

Related Issue and PR

• [Feature]: Add Frontend Repository Integration with Git Submodule and Pixi Environment #44
• feat: Prefect implementation and alembic reset #46