dsp-job-data-migration — Overview¶

Job that copies geographic data from your organization’s database into the DSP, automatically and repeatably. Orchestration via dsp-core (./setup.sh). Database detail: Databases.

Summary¶

• How it works
• What gets copied
• Flow at a glance
• Copy order
• KPI calculation
• Only what changed since last time
• What happens on each run
• Map and downloads
• When the job runs day to day
• Technical detail (quick reference)
• Where to go deeper

How it works¶

Think of the organization database as the working folder where registrations are updated, and the DSP needs its own copy so the public site stays fast without exposing that database directly on the internet.

The migration job does four main things:

1. Reads the source database (read-only — it does not change anything there).
2. Writes in two places inside the DSP:
3. A light database used by the API and screens (names, dates, a rectangle and a map point per record — not the full polygon).
4. Another database with full geometry of properties and territories, used for the map and heavy exports.
5. On the next run, it does not copy everything again — only records new or changed since the last successful copy (this is the watermark, a “bookmark” kept by the job itself).
6. After the copy, it calculates Home KPIs. kpiCalculationJob reads geometries in the map database, writes each property’s area to dsp.area_of_interest.area, and writes per-theme indicators to dsp.kpi_measure — light database only. That area does not come from the source.

If a record disappears at the source, the job can also remove the copy in the DSP (periodic “orphan” sweep).

It is not the program that publishes layers on the site map: ./setup.sh does that after data is already in the databases. It also does not generate large download files in S3 storage — it only flags which regions need them; the geo-file job generates the files.

What gets copied¶

Names “level 1, 2, 3” are generic: what each level means depends on the tables you configured, not a fixed code convention.

Flow at a glance¶

flowchart LR
  origem[(Organization database)]
  job[Migration job]
  leve[(DSP light database)]
  mapa[(Map database)]

  job -->|reads| origem
  job -->|writes text and geo summary| leve
  job -->|writes full geometry| mapa

In Compose, the databases are named dsp-db (light) and dsp-geoserver-db (map).

Copy order¶

Copy follows hierarchy, like assembling a puzzle from the inside out:

level 1 → level 2 → level 3 → properties (area of interest) → extra layers (if any) → KPI calculation

The parent must exist before the child (for example, municipality before the property inside it). Theme KPIs depend on layer geometries in the map database. kpiCalculationJob runs last (@Order(3)).

Only what changed since last time¶

On the first load, records with a filled creation date at the source are included (as configured in ./config.sh).

On later loads, the job compares against the date/time of the last successfully completed run:

• Copies what was created after that mark.
• If the source has an update column, also copies what was changed after that mark.

If nothing changed, the run finishes quickly (“no work”). The mark only advances when the job finishes without error — so the DSP does not “skip” data if something failed midway.

Timezone for source dates: usually America/Sao_Paulo, unless configured differently in the YAML.

What happens on each run¶

In simple terms, the job always repeats the same script:

flowchart TD
  A[1. Check what changed at source] --> B{2. Anything new or updated?}
  B -->|no| C[Exit without copying again]
  B -->|yes| D[3. Copy in chunks to avoid blocking]
  D --> E[4. Update both DSP databases]
  A --> F[From time to time: remove what disappeared at source]

Technically this is watermark-based detection, skip-or-process decision, paged reads, and writes with “update if exists, else insert” (UPSERT).

Map and downloads¶

When a migration completes successfully, the job marks which states/municipalities (levels 2 and 3) need a new download file. If migration fails, those marks do not change — avoids publishing downloads with incomplete data.

When the job runs day to day¶

This is defined by ./setup.sh, not the ./config.sh wizard:

Each job “run” is a process that starts, works, and exits; in continuous mode, a scheduler inside the container triggers those runs.

Technical detail (quick reference)¶

Running the JAR standalone (without core): Java 21, ./mvnw, four accessible databases, and application.yaml generated by ./config.sh (dsp-core repository).

KPI calculation¶

kpiCalculationJob (@Order(3)) runs after AOI and layer migration. It reads geometries from geo-target, writes results to dsp-db, and does not change geo-target. The area column exists on the AOI DDL in dsp-db, but it is not migrated from the source: there is no area_column or total-area-column in the contract. The migration job writes bbox, centroid, and attributes; the KPI job computes ST_Area(geom::geography) on geo-target and updates dsp.area_of_interest.area. The displayed unit comes from kpis.area-unit-of-measurement (wizard, stage 5).

flowchart LR
  geo[(geoserver-db<br/>geom)]
  job[kpiCalculationJob]
  dsp[(dsp-db)]
  api[TotalizerService]

  geo -->|ST_Area AOI| job
  geo -->|ST_Area per layer| job
  job -->|UPDATE area| dsp
  job -->|TRUNCATE + INSERT| dsp
  dsp --> api

Prerequisites¶

• AOI job finished with valid geom on dsp.area_of_interest in geo-target.
• For each theme in kpis.themes[], the matching layer already migrated in geo-target (dsp.<layer_name>).
• kpis.theme-count equal to the number of kpis.themes[] entries with layer-name set.

What the job writes¶

With theme-count: 0, the job still updates AOI area and leaves kpi_measure empty (after truncate).

Schema dsp.kpi_measure¶

Created on dsp-db if it does not exist yet. It does not exist on geo-target.

Constraint UNIQUE (area_of_interest_id, kpi_name).

For each theme, the job groups layer features by area_of_interest_id on geo-target and sums ST_Area(geom::geography) before unit conversion. Theme KPIs are not AOI columns (theme_1…theme_4 / business-only-persist-columns do not play that role).

Contract with installation¶

Three artifacts must stay aligned:

• adopter-config.yaml: installation.kpis.theme_count, theme_1…theme_4.layer, area_of_interest.optional_label
• application.yaml: kpis block + execution-jobs.kpi-job: true (generated by ./config.sh)
• installation-config.json: AREA_OF_INTEREST + THEME_* cards with a layer field on themes

./config.sh always enables L1, L2, L3, area of interest, and kpi-job. Outside the core, enable execution-jobs.kpi-job and configure the kpis block if Home uses theme cards. The backend aggregates values in POST /totalizer/ — see dsp-backend.

kpis block in application.yaml¶

Generated by ./config.sh from installation.kpis in adopter-config.yaml. Example with two themes:

kpis:
  theme-count: 2
  area-unit-of-measurement: ha
  themes:
    - slot: 1
      layer-name: rivers
      unit-of-measurement: ha
    - slot: 2
      layer-name: conservation-units
      unit-of-measurement: m²

execution-jobs:
  kpi-job: true

Required order in application.yaml is L1 → L2 → L3 → area-of-interest → layers → kpi-job. The KPI job is not part of dual-write: it is a tasklet that reads geo-target and updates area + kpi_measure on dsp-db after jobs @Order(1) and @Order(2).

Where to go deeper¶