Databases¶

Contract for where data lives in the DSP — from the adopter source to download files. Step-by-step between components is in Data flow; layer view in Architecture.

Summary¶

• All databases and repositories
• Compose Postgres detail
• Diagram
• Connection roles
• Schema dsp — business and lightweight geo
• Batch schemas on dsp-db
• Territorial download pending work
• Readers and writers
• Migration dual-write
• SRID via YAML
• Spring prefixes (jobs)

All databases and repositories¶

In the full DSP flow (real adopter with JDBC source), there are four distinct data destinations, plus the origin:

flowchart LR
  src[("Source database<br/>(adopter)")]
  mig[Migration job]
  dsp[("dsp-db")]
  gsdb[("dsp-geoserver-db")]
  geoJob[Geo-file job]
  s3[("SeaweedFS")]

  mig -->|read| src
  mig -->|write business + bbox/centroid| dsp
  mig -->|write geom · separate Postgres| gsdb
  geoJob -->|read flags| dsp
  geoJob -->|read geom| gsdb
  geoJob -->|write CSV| s3

Each migration run performs dual-write: one operational destination (dsp-db, no full polygon) and another database (dsp-geoserver-db) only for integral geometry.

In the local demo (Quick start) there is no external source database: setup.sh applies synthetic seed directly to both Postgres instances. Object storage and the geo-file job are usually off; small downloads may use GeoServer Download only.

Compose Postgres detail¶

The two SQL databases are separate Postgres instances in Compose. They repeat the same logical tables in the dsp schema, but migration does not write geom to dsp-db — full geometry goes only to dsp-geoserver-db (detail in the next section).

Inside dsp-db there are three schemas:

Each batch job uses its own schema on the same Postgres so execution history does not mix.

Diagram¶

Arrows follow who performs the operation: read and write leave the component (job, backend, GeoServer) and point to the database or repository.

flowchart LR
  src[(Adopter JDBC<br/>source)]
  mig[dsp-batch<br/>migration]
  jobGeo[geo-file job]
  dsp[(dsp-db<br/>dsp + batch schemas)]
  ex[(dsp-geoserver-db<br/>full geom)]
  s3[(SeaweedFS)]
  api[backend]
  gsEx[GeoServer Exhibition]
  gsDl[GeoServer Download]

  mig -->|read| src
  mig -->|write business + bbox/centroid<br/>batch + flags| dsp
  mig -->|write full geom| ex
  jobGeo -->|read flags| dsp
  jobGeo -->|write batch + clear flags| dsp
  jobGeo -->|read geom| ex
  jobGeo -->|write CSV| s3
  api -->|read business| dsp
  api -->|read CSV| s3
  api -->|read WFS fallback| gsDl
  gsEx -->|read layers| ex
  gsDl -->|read layers| ex

Connection roles¶

These are datasource roles, not extra databases. The migration job opens four Java connections: source, target, geo-target, and batch (the last on the same host as target, schema data_migration).

The geo-file job also uses target (territory flags) and geo-target (export), with batch in geo_file_generation.

Object storage (SeaweedFS) is not Postgres: backend and geo-file access it via S3 API when the object-storage profile is active.

Schema dsp — business and lightweight geo¶

The same logical tables exist on both Postgres instances (territory_level_1, territory_level_2, territory_level_3, area_of_interest, and configured layers). Territory IDs are VARCHAR(64); AOI/layers use VARCHAR(255) for id.

The job derives boundary_box and centroid_coordinates at the source and writes them only to dsp-db. Full geometry goes to geom on geoserver-db.

The area column on dsp.area_of_interest and the dsp.kpi_measure table exist only on dsp-db (not on geo-target). Area is not part of dual-write: kpiCalculationJob computes it from geom on geo-target. Theme KPIs are not AOI columns either.

created_at on the target is required (watermark base). updated_at is populated when the YAML declares updated-at-column.

Why not store the whole polygon in dsp-db? See the note in Architecture — Data flow.

Batch schemas on dsp-db¶

Init SQL for these schemas comes from dsp-core (dsp-db and job images). Operational detail: dsp-core.

Territorial download pending work¶

Columns only on territory_level_2 and territory_level_3 on dsp-db:

Summary flow:

1. Migration processes the delta defined by the watermark (creation/update since last success).
2. If the run ends COMPLETED, it marks territories affected in the same time window (requires_s3_file_regeneration = true). On first load, it marks the relevant territorial set.
3. Geo-file, on the schedule configured in setup, processes only pending items, publishes to SeaweedFS, and clears the flag per territory when all formats finish.

If migration fails, flags do not change. More context: dsp-job-geo-file-generation.

Readers and writers¶

Both GeoServers use only dsp-geoserver-db (separate processes, same dataset).

Migration dual-write¶

Each delta run performs one read on the origin and two writes to different databases (generic layers may write only to geo-target):

1. Read attributes + geometry from the JDBC source (watermark + where-clause).
2. UPSERT on dsp-db: attributes + boundary_box + centroid_coordinates — without geom column.
3. UPSERT on dsp-geoserver-db (separate Postgres): same attributes + full geom.

kpiCalculationJob is not part of this dual-write: after the AOI and layer jobs, it reads geo-target and updates area and kpi_measure on dsp-db only.

flowchart LR
  job[dsp-batch] -->|1. read| src[(JDBC source)]
  subgraph w ["2. write to two Postgres instances"]
    job -->|write without geom| dsp[(dsp-db)]
    job -->|write full geom| ex[(dsp-geoserver-db)]
  end

Incremental watermark: Job overview.

SRID via YAML¶

SRID is not fixed in code. In core administrative unit DDL, geom may go without typmod; auto-generated AOI/layers may include SRID in CREATE TABLE.

Different layers may use different SRIDs if source and YAML are aligned.

In the Brazilian context, SRID 4674 (SIRGAS 2000) is common for official geospatial data.

Spring prefixes (jobs)¶

Migration job overview: Overview. Checks after load: Post-migration validation.