# avniproject/avni-etl architecture

> Architecture diagram of the GitHub repository avniproject/avni-etl, generated by GitDiagram from its file tree, README and a sample of source files (last updated 2025-09-11). It is an AI-made overview: check details against the source.

- Interactive diagram: https://gitdiagram.com/avniproject/avni-etl
- Repository: https://github.com/avniproject/avni-etl

## Overview

##### Project type and purpose
- This is a standalone backend ETL microservice written in Java (Spring Boot, Gradle).
- Its purpose is to extract data from a primary Avni Postgres database (public schema), transform it into flat, analytic-friendly tables in per-organization ETL schemas, and optionally synchronize media assets via S3.
- It also exposes REST endpoints for job control, reporting, and media management, and runs scheduled jobs via Quartz.

##### File structure
- Top-level build & CI: Makefile, build.gradle, .circleci/config.yml, .github/workflows → Continuous integration & deployment pipelines.
- src/main/java/org/avniproject/etl:
- config – Spring configurations (Amazon S3, Cognito, Keycloak, scheduler).
- controller – REST controllers: Ping, MediaController, ReportController, backgroundJob controllers.
- scheduler – Quartz job definitions (EtlJob, MediaAnalysisJob).
- service – Business logic orchestrating ETL, schema migrations, sync, reporting.
- repository – Data access layer with Spring JDBC, custom SQL builders, row mappers, sync actions.
- domain & dto/contract – Entities, context holders, metadata model, DTOs for API & job contract.
- security – IAM/Auth filters & helpers.
- util – Common utilities (DTO mapping, logging, string/date helpers).
- src/main/resources: SQL scripts for ETL, post-ETL adjustments, application.properties, Postman collection.

##### README for additional insights
- Describes RLS and JSONB transformation into flat tables, per-organization schemas.
- Mentions scheduled jobs and metadata tables (table_metadata, column_metadata, index_metadata) in the public schema.
- Explains developer setup: avni-server database, make commands.

##### Main components
a. External Systems
- Avni Primary Postgres DB (public schema + application schemas)
- S3 Bucket (for media files)
- Identity Providers (AWS Cognito; Keycloak via AvniKeycloakConfig)
- CI/CD system (CircleCI/GitHub Actions)
b. ETL Service (Spring Boot Application)
- REST API Layer (Controllers)
- Scheduler Layer (Quartz jobs)
- Service Layer (EtlService, SyncService, MediaService, ReportingViewService, etc.)
- Data Access Layer (Repositories, RowMappers, SQL Generators)
- Config Layer (AmazonClientService, CognitoConfig, ScheduledJobConfig)
- Domain & Metadata Store (table_metadata, column_metadata, index_metadata tables in public schema)
c. Data Stores
- Metadata Tables (public.table_metadata, ... )
- Entity Sync Status (public.entity_sync_status)
- ETL Schemas (per-organisation Postgres schemas)
d. Build & Deployment
- Gradle Wrapper, Makefile
- CI/CD pipelines (.circleci, GitHub workflows)
- deploy/start.sh script

##### Relationships & interactions
- REST API receives job control or reporting requests → Controllers invoke Service Layer → Service Layer calls Repository Layer → Queries/updates the DB.
- Scheduler triggers Quartz jobs on schedule → Job implementations call Service Layer to perform incremental sync.
- Service Layer reads JSONB-heavy tables in public schema → Transforms & upserts flat tables in per-org ETL schemas → Updates metadata tables & entity_sync_status.
- Media sync flows: Repository actions read media metadata → AmazonClientService uploads/downloads to/from S3 → MediaController serves media download requests.
- Security filters authenticate API requests against Cognito or Keycloak before invoking controllers.

##### Architectural patterns & design principles
- Layered architecture (Controller → Service → Repository).
- Dependency Injection via Spring Boot.
- Quartz for scheduling; externalized job configuration.
- SQL-centric data access with custom builders and row mappers (no heavy ORM).
- Metadata-driven schema migration (table/column/index metadata tables drive DDL adjustments).

##### Additional notes
- Metadata management: highlight the feedback loop where Service Layer updates table_metadata & index_metadata and then adjusts DDL accordingly.
- If desired, include a small inset showing CI/CD pipeline steps (build → test → deploy).
- Error handling path: AuthenticationFilter → ApiSecurity → controllers (optional as a secondary flow).

## Diagram (Mermaid)

```mermaid
flowchart TD
    subgraph "External Systems"
        AvniDB["Avni Primary Postgres DB\n(public schema & app schemas)"]:::db
        S3Bucket["S3 Bucket"]:::db
        AWS_Cognito["AWS Cognito"]:::external
        Keycloak["Keycloak"]:::external
        CICD["CI/CD System"]:::external
        BuildDeploy["Build & Deployment\n(Gradle, Makefile, scripts)"]:::external
    end

    EtlApplication["EtlApplication\n(Spring Boot)"]:::app

    subgraph "Configuration Layer"
        Config["Configuration\n(S3, Cognito, Keycloak, Scheduler)"]:::config
    end

    subgraph "API Layer"
        Controllers["REST API Layer\n(Controllers)"]:::api
    end

    subgraph "Scheduler Layer"
        Scheduler["Scheduler Layer\n(Quartz Jobs)"]:::scheduler
    end

    subgraph "Service Layer"
        Service["Service Layer\n(Business Logic)"]:::service
    end

    subgraph "Data Access Layer"
        Repository["Data Access Layer\n(Repositories, SQL Builders, RowMappers)"]:::repo
    end

    subgraph "Domain & Metadata"
        MetadataModels["Domain & Metadata Models\n(Entities & Metadata Tables)"]:::domain
        DomainEntities["Domain Context & Entities"]:::domain
    end

    subgraph "DTOs & Contracts"
        DTOs["DTOs & Contracts"]:::dto
    end

    Security["Security Layer\n(Auth Filters, IAM Helpers)"]:::security
    Utils["Utility Classes\n(DTO mapping, logging, helpers)"]:::util

    subgraph "SQL Scripts"
        SQL_ETL_Scripts["SQL Scripts for ETL"]:::scripts
        PostETL_Scripts["Post-ETL Adjustment Scripts"]:::scripts
    end

    AppConfig["Application Config\n(properties & Postman)"]:::scripts

    AvniDB -->|"read JSONB tables"| Repository
    Repository -->|"write flat tables"| AvniDB
    Repository -->|"upload/download media"| S3Bucket
    S3Bucket -->|"media stream"| Controllers

    Controllers -->|"invoke"| Service
    Scheduler -->|"trigger job"| Service
    Service -->|"call"| Repository
    Service -->|"update metadata"| MetadataModels
    MetadataModels -->|"drive migrations"| Repository

    Config --> EtlApplication
    Security --> Controllers

    EtlApplication --> Controllers
    EtlApplication --> Scheduler
    EtlApplication --> Service
    EtlApplication --> Repository

    CICD -->|"build & deploy"| BuildDeploy
    BuildDeploy --> EtlApplication

    classDef external fill:#d3d3d3,stroke:#333,stroke-width:1px;
    classDef db fill:#f4a261,stroke:#333,stroke-width:1px,stroke-dasharray: 5 5;
    classDef app fill:#2a9d8f,stroke:#333,stroke-width:1px;
    classDef config fill:#81b29a,stroke:#333,stroke-width:1px;
    classDef api fill:#8ecae6,stroke:#333,stroke-width:1px;
    classDef scheduler fill:#219ebc,stroke:#333,stroke-width:1px;
    classDef service fill:#52b788,stroke:#333,stroke-width:1px;
    classDef repo fill:#e76f51,stroke:#333,stroke-width:1px;
    classDef domain fill:#ffb703,stroke:#333,stroke-width:1px;
    classDef dto fill:#a7c957,stroke:#333,stroke-width:1px;
    classDef security fill:#023047,stroke:#fff,stroke-width:1px;
    classDef util fill:#bde0fe,stroke:#333,stroke-width:1px;
    classDef scripts fill:#ffdd57,stroke:#333,stroke-width:1px;

    click EtlApplication "https://github.com/avniproject/avni-etl/blob/main/src/main/java/org/avniproject/etl/EtlApplication.java"
    click Controllers "https://github.com/avniproject/avni-etl/tree/main/src/main/java/org/avniproject/etl/controller"
    click Scheduler "https://github.com/avniproject/avni-etl/tree/main/src/main/java/org/avniproject/etl/scheduler"
    click Service "https://github.com/avniproject/avni-etl/tree/main/src/main/java/org/avniproject/etl/service"
    click Repository "https://github.com/avniproject/avni-etl/tree/main/src/main/java/org/avniproject/etl/repository"
    click Config "https://github.com/avniproject/avni-etl/tree/main/src/main/java/org/avniproject/etl/config"
    click MetadataModels "https://github.com/avniproject/avni-etl/tree/main/src/main/java/org/avniproject/etl/domain/metadata"
    click DomainEntities "https://github.com/avniproject/avni-etl/tree/main/src/main/java/org/avniproject/etl/domain"
    click DTOs "https://github.com/avniproject/avni-etl/tree/main/src/main/java/org/avniproject/etl/dto"
    click DTOs "https://github.com/avniproject/avni-etl/tree/main/src/main/java/org/avniproject/etl/contract"
    click Security "https://github.com/avniproject/avni-etl/tree/main/src/main/java/org/avniproject/etl/security"
    click Utils "https://github.com/avniproject/avni-etl/tree/main/src/main/java/org/avniproject/etl/util"
    click SQL_ETL_Scripts "https://github.com/avniproject/avni-etl/tree/main/src/main/resources/sql/etl"
    click PostETL_Scripts "https://github.com/avniproject/avni-etl/tree/main/src/main/resources/post-etl"
    click AppConfig "https://github.com/avniproject/avni-etl/blob/main/src/main/resources/application.properties"
    click AppConfig "https://github.com/avniproject/avni-etl/blob/main/src/main/resources/api/AVNI ETL.postman_collection.json"
    click CICD "https://github.com/avniproject/avni-etl/blob/main/ .circleci/config.yml"
    click CICD "https://github.com/avniproject/avni-etl/tree/main/ .github/workflows"
    click BuildDeploy "https://github.com/avniproject/avni-etl/blob/main/build.gradle"
    click BuildDeploy "https://github.com/avniproject/avni-etl/tree/main/Makefile"
    click BuildDeploy "https://github.com/avniproject/avni-etl/tree/main/gradlew"
    click BuildDeploy "https://github.com/avniproject/avni-etl/blob/main/deploy/start.sh"
```
