CAPCON Database Help

Connectivity to the EXTERNAL client databases (Β§7) β€” the paying agency's own systems, reached over VPN.
← All apps

CAPCON Database

Connectivity to the EXTERNAL client databases (Β§7) β€” the paying agency's own systems, reached over VPN.

Connectivity to the EXTERNAL client databases (Β§7) β€” the paying agency's own systems, reached over VPN. It is the only component that reads from or writes to anything outside CAPCON.

What it does

Space on the database server

The Overview panel checks how much space is left on the client database server of the selected project (on the disk that holds the PostgreSQL data directory). At 3 % or less it shows the warning "Only N % (X GB) left. Clean up the database." The percentage is computed the same way as the Use% column of df -h: 3 % free means Use% 97 %.

Below it are listed the databases and the 15 largest tables of the project's database. They help decide what to clean up (for example old master backup copies).

The space is checked over the database connection (COPY ... FROM PROGRAM 'df'), so the database account must be a superuser. Otherwise only the database sizes are shown.

Panels

Use the menu on the left; each panel has its own help topic.

Client Connections

Status: not written yet. This topic is a stub created with the project template. The panel's owner (see .github/CODEOWNERS) fills it in as the panel is built. Delete this quote block when you do.

What this panel is for

TODO β€” one or two sentences: what question does this panel answer, and at what point in the CAP-control workflow does an operator open it?

The manifest describes it as: Configure and test connections to external client databases over VPN.

Before you start

TODO β€” what must already be true. Most panels here need a project selected in the top bar, and the step before them to have completed.

How to use it

  1. TODO β€” the first thing the operator does.
  2. TODO β€” the second.
  3. TODO β€” how they know it worked.

What it writes

TODO β€” name the outputs exactly: which table, which CSV, which folder under the project tree. An operator reading this later needs to find the artefact without reading the code.

When it goes wrong

TODO β€” the failure you actually hit while building this, and the check that distinguishes it from the next-most-likely cause. This section is the reason the help system exists; a list of buttons is not.

Retrieve Layers

Status: not written yet. This topic is a stub created with the project template. The panel's owner (see .github/CODEOWNERS) fills it in as the panel is built. Delete this quote block when you do.

What this panel is for

TODO β€” one or two sentences: what question does this panel answer, and at what point in the CAP-control workflow does an operator open it?

The manifest describes it as: Retrieve updated vector and attribute layers from the client database.

Before you start

TODO β€” what must already be true. Most panels here need a project selected in the top bar, and the step before them to have completed.

How to use it

  1. TODO β€” the first thing the operator does.
  2. TODO β€” the second.
  3. TODO β€” how they know it worked.

What it writes

TODO β€” name the outputs exactly: which table, which CSV, which folder under the project tree. An operator reading this later needs to find the artefact without reading the code.

When it goes wrong

TODO β€” the failure you actually hit while building this, and the check that distinguishes it from the next-most-likely cause. This section is the reason the help system exists; a list of buttons is not.

NMA master table

What this panel is for

It creates the year's declarations master table nma_<metai>.nma_adm_<metai>_master in the client database from NMA data, and later updates it with new packages. It is database/specs/create_master.sql turned into a form. Every Checks algorithm uses this table, so it is created at the start of the year and updated whenever a new package arrives.

Before you start

client database set in the Client Connections panel (e.g. sagris_lit).

(sudo swanctl --initiate --child net240). The NMA data (pvsad.*) are oracle_fdw tables in the itree_nma database, and they do not answer without the tunnel.

oracle_fdw user mapping for the server pvsad in the itree_nma database. If it does not, "Test connection to NMA" shows user mapping not found, and a database administrator has to create it.

in the project's client_db.source_dbname.

The VPN tunnel

With "Manage the VPN automatically" ticked, the job brings the NMA VPN tunnel up on the x51 server (if it is down) and takes it down after a successful run (swanctl --terminate --ike itree). If the job fails, the tunnel is left up so that you can try again; take it down with the "Take down VPN" button. The "VPN status", "Bring up VPN" and "Take down VPN" buttons also let you manage the tunnel by hand.

The tunnel is managed through the database connection, not over SSH. x51 sits behind a router, and only the PostgreSQL port is forwarded from outside (54321 β†’ 5432). The database account (sopr) is a superuser, so through COPY … FROM PROGRAM it can run one program on x51 as the user postgres. CAPCON sends only one word (up, down or status), and the x51 script turns it into a swanctl command. If the account ever lost its superuser rights, tunnel management would stop working.

Setting it up on x51 (once, as root). The panel shows the exact contents:

  1. /usr/local/bin/capcon-vpn: the script, owner root, chmod 755.
  2. /etc/sudoers.d/capcon-vpn (edit with visudo -f). This file lets the user

postgres run only that script, and only with up, down or status.

  1. Check: sudo -u postgres sudo -n /usr/local/bin/capcon-vpn status.
  2. In the panel, press "VPN status", then tick "Manage the VPN automatically".

How to use it

  1. Test connection to NMA. If the connection works, the latest NMA

packages are shown (pak_id, pak_numeris, …).

  1. Current master: the summary shows which packages are already loaded and

how many fields have no ppz_danga.

  1. Code lists (ARZ, GAN, SOD, NET, PSL, SNV, PDJ). They can be edited only

after pressing "Edit code lists". Saved lists stay with the project (project.yaml, master_codes). A code may be in only one ppz_danga list and only one ppz_kodas list.

  1. Choose the action:

are loaded, or only the ones given. If a master already exists, it is not deleted: it is renamed to nma_adm_<metai>_master_iki<MMDD>, and a new one is created with the same columns. You confirm this with a tick.

  1. Tick "I have checked the code lists…" and press Run.

The run is a background job on the server: the panel only starts it and shows new log lines every 2 s. Closing the browser or reloading the page does not stop the job; when you open the panel again, it re-attaches to the running job. You can cancel it with the ⏹ Stop button of the log window: the job stops within ~20 s, and the master is rolled back to its state before the run.

How packages are applied

Packages are applied in ascending pak_id order:

glk_dkl_id from an older (or the same) package. If the master has no such record, they are inserted;

or GEO again, the field is inserted again;

The summary shows them separately;

Then the whole master is recomputed: area_ha, perim_m, centr_x/y, ppz_danga from the lists (NULL for other codes), ppz_danga_reiksme = NULL and ppz_kodas from the lists (NULL for others).

What it writes

WhereWhat
nma_<metai>.pvsad_new_data_<MMDD>staging table for the NMA data unloaded that day. Dropped once the master is saved; it stays only if the master step fails (so that you can check what NMA returned). Nothing is created in the itree_nma database
nma_<metai>.nma_adm_<metai>_masterthe master table, with a unique index on glk_dkl_id and a spatial index
nma_<metai>.nma_adm_<metai>_master_iki<MMDD>the previous master, if a new one was created
<project>/vectors/nma_adm_<metai>_prod_statistikai_<MMDD>.gpkgfor Nerijus, for computing statistics (glk_dkl_id, glk_paselio_kodas, geometry)
<project>/vectors/lit_laukai_<metai>_<MMDD>.gpkgsend to Giedrius: LT parcels with geometries
<project>/logs/master_<action>_<date_time>.jsonthe run record: parameters, code lists, summaries

<MMDD> is the unload date. An existing GPKG is not overwritten: _v2, _v3… is written instead (the layer name is the same as the file name). A second NMA table unloaded on the same day gets _v2.

When it goes wrong

works, but the account has no oracle_fdw user mapping for the server pvsad. This is not a VPN problem.

not up.

unchanged.

none of the lists. Codes NTM, MVP, Ε½M-1…ŽM-7, Ε PA and the like are left out of the lists on purpose, so this is not an error. Pay attention only to a new code you have not seen before.

(e.g. during a deploy) while the job was running. The master transaction was rolled back; run it again.

master stays as it was. During the run the master is locked for writing, so other jobs that change it wait.

pvprod schema reload

What this panel is for

In the itree_nma database, the schema pvprod consists of oracle_fdw foreign tables that point to the NMA Oracle schema "PVSAD" (server pvprod). This panel drops that schema and imports it again.

Do this ONLY when the table structure has changed on the Oracle side: a new table has appeared, a column has been added, renamed or changed, or a query returns ORA-00904: invalid identifier. When only the data has changed, no reload is needed: foreign tables read their data from Oracle on every query.

CAPCON itself reads pvsad.*, not pvprod.*, so this reload has no effect on CAPCON's own work.

What it does

The owner's commands:

DROP SCHEMA pvprod CASCADE;
VACUUM;
CREATE SCHEMA pvprod;
IMPORT FOREIGN SCHEMA "PVSAD" FROM SERVER pvprod INTO pvprod;

The panel runs them with three differences. All of them guard against losing anything:

(VPN down, ORA-12170, no user mapping), pvprod stays as it was. Run one by one, the commands would leave no schema at all. VACUUM cannot run inside a transaction, so it is run after it. The result is the same.

together with the tables. Previously, after a reload, ALTER TABLE … OWNER TO sopr and similar commands had to be run by hand. Now they are read before the drop and restored, in the same transaction, for every table that comes back.

tables, CASCADE would drop them too. "Preview" shows them, and the reload is possible only after you tick that you agree to it.

How to use it

  1. Select the year and project in the top bar. The project must have a client

database set (Client Connections).

  1. Press "Preview". It only reads; no VPN is needed. It shows whether the

server pvprod and the user mapping for the role exist, how many tables there are, who owns them, and what CASCADE would drop outside the schema.

  1. The VPN tunnel must be up, because IMPORT fetches the table

definitions from Oracle. If automatic VPN management is on in the NMA master table panel, the job brings the tunnel up itself and takes it down after a successful run. The tunnel is not taken down while an NMA master table job is running.

  1. Tick the confirmation and press "Reload pvprod". The log shows how many

tables were imported, how many owners and privileges were restored, which tables are new and which are gone.

Limitations

"is already being reloaded".

finish in the remaining time, it is cut off. The schema has already been reloaded by then, so this does no harm, and VACUUM can be run by hand.

created by hand) are not restored by IMPORT. "Preview" shows them.

Result Packages

Status: not written yet. This topic is a stub created with the project template. The panel's owner (see .github/CODEOWNERS) fills it in as the panel is built. Delete this quote block when you do.

What this panel is for

TODO β€” one or two sentences: what question does this panel answer, and at what point in the CAP-control workflow does an operator open it?

The manifest describes it as: Assemble current results into delivery packages.

Before you start

TODO β€” what must already be true. Most panels here need a project selected in the top bar, and the step before them to have completed.

How to use it

  1. TODO β€” the first thing the operator does.
  2. TODO β€” the second.
  3. TODO β€” how they know it worked.

What it writes

TODO β€” name the outputs exactly: which table, which CSV, which folder under the project tree. An operator reading this later needs to find the artefact without reading the code.

When it goes wrong

TODO β€” the failure you actually hit while building this, and the check that distinguishes it from the next-most-likely cause. This section is the reason the help system exists; a list of buttons is not.

Deliver to Client

Not yet tried against the real databases. Sending a package to NMA is disabled for now: the checks can be run, but the package is not sent to pvsad.pvsad_poz.

What this panel is for

  1. Deliver a LIT package: check the package that the latest CAPCON

algorithm (Checks) left in the table nma_<metai>.pvsad_pak_pozymiai, and deliver it to the NMA table pvsad.pvsad_poz.

  1. ALNSIS 2 GPKG files: see the paths of the GPKG files that the ALNSIS 2

algorithms produced for the selected period.

Before you start

list).

algorithm clears the pvsad_pak_pozymiai table and fills it again, so it always holds only the latest run's package.

taken down after the NMA queries (as in the NMA master table panel; this needs the capcon-vpn script on the x51 server).

How to use it

  1. Choose the algorithm whose package you are delivering.
  2. Optionally, enter package numbers to compare with, e.g. from earlier years.

The previous package of this algorithm is found in NMA and added automatically.

  1. Run checks. The package number (ppz_pak_id), the data date

(ppz_pagrindo_data) and the delivery date (ppz_skaiciavimo_data) are shown, followed by the table of check results.

  1. Once sending is enabled, the Deliver package to NMA button becomes

active only when there is not a single error (βœ—).

Checks

TestWhat is checked
Columns and typesthe source has every column the INSERT reads, pvsad.pvsad_poz has every column it writes, and the types are compatible
NULL / empty cellsa column is filled in every row, or not used by this algorithm. A partly empty column is an error unless it is a known exception (e.g. DGP < 0.1 ha). Exceptions are shown with "!" and must be named in the e-mail to NMA
Number of declared fieldsthe row count equals the number of master table fields the algorithm covers. No duplicate, missing or extra fields
Number of crop groupsthe package contains every crop of those master fields (glk_paselio_kodas, DISTINCT)
IDs not doubleppz_id, ppz_dkl_id, ppz_pak_id, ppz_pak_pak_id are not double, neither in the source nor in NMA
Crop classificationppz_kodas / ppz_danga hold only the algorithm's values (e.g. only PSL, not PDJ)
Probabilities validprobabilities are between 0 and 1, present where and only where there is a crop, q<n>_proba + q<n>_proba2 ≀ 1
Staging tableitree_nma has public.pvsad_pak_pozymiai with every column the INSERT reads
Comparison with packageswhich columns are filled in this package and in the NMA packages. Differences are shown with "!"

The number of fields is checked against the master table, not against the "declared fields" spreadsheet sheet.

What it writes

The checks write nothing. The delivery (once enabled) works like checks/specs/PDJ/PDJ_0630.sql:

  1. In the itree_nma database it clears the staging table

public.pvsad_pak_pozymiai (TRUNCATE) and copies the package into it from sagris_lit nma_<metai>.pvsad_pak_pozymiai. Columns are copied by name. It checks that the row count is the same and the package is the same, and the table is kept. It can be inspected after the delivery. Note: between deliveries it holds the last delivered (or attempted) package, not necessarily the one now in sagris_lit;

  1. if the package is not yet in pvsad.pvsad_poz, it runs the INSERT into

pvsad.pvsad_poz from the staging table. The query can be seen in the panel ("INSERT query"). Before the commit it checks that the package is visible and is the latest one. If not, the INSERT is rolled back and nothing is sent to NMA;

  1. it appends the package's records to that year's delivered-packages table

nma_<metai>.pakeliai_<first>_<last> and renames it after the package just delivered. The first time in a year the table is created from the first delivered package, e.g. nma_2026.pakeliai_334_334 β†’ after package 335 nma_2026.pakeliai_334_335, and so on.

The delivered-packages table is updated only when the package has been delivered to the iTree database and that check has passed. If the same package is already in the table, the check "Package not yet delivered" shows an error and the delivery is refused. The schema must hold only one pakeliai_*_* table.

When it goes wrong

client database): no Checks algorithm has been run for this year yet.

algorithm's package. Choose that algorithm, or run the one you want again.

Database β†’ NMA master table, the VPN section.

bar.