How avniproject/avni-etl works
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).