Skip to main content

NomaUBL Database

This screen configures the access to the database NomaUBL uses to store its own data: JDBC connection settings, credentials, schema, table names, the column-name mapping, and a one-click bootstrap. Both Oracle and PostgreSQL are supported.

The tables hold invoice headers, lines, VAT summary, lifecycle events, validation results, runtime logs, e-reporting data, the auth model (users / roles / sessions / permissions) and the notifications inbox. The default names follow the JDE convention (F564xxx) — a legacy of NomaUBL's original deployment alongside JD Edwards — but every table name and every column name is configurable, so the connector works equally well in a non-JDE context.

The editor has four tabs:

  1. Connection — database type, JDBC URL, credentials.
  2. Tables — schema, the full table catalogue, the Validate Schema panel and the detail-storage strategy.
  3. Columns — per-table column-name overrides resolved at runtime via UBLColumnConfig.
  4. Initialize — one-click creation of every NomaUBL table and provisioning of the default admin user and roles.
Refreshed in 2026.05.5
  • All tables are now first-class entries — the auth tables (F564250 users, F564251 roles, F564252 sessions, new F564254 permissions) and the notifications table (F564253) joined the catalogue under the Tables tab. They participate in DDL substitution if renamed, on the same footing as invoice / e-reporting tables.
  • E-Reporting tables renumbered — F564240 / F564241 / F564242 became F564260 / F564261 / F564262. The XML payload column moved from TEXT / CLOB to BYTEA / BLOB (UTF-8 bytes via dialect.writeBlob).
  • Validate Schema panel — a new section under Tables compares the bundled DDL (the canonical .sql files inside the JAR) against the live schema using the names configured here. Reports missing tables, missing or extra columns, and type / size mismatches. Read-only — never alters the schema. Same machinery as the -validate-ddl CLI mode.
  • Columns tab — dedicated tab to override column names per table; resolves through UBLColumnConfig so customer-renamed columns no longer drift silently.
  • Init log fills the height — the Initialize tab's log area now flexes to fill the available space instead of capping at 200 px.

Opening the editor​

  • Sidebar → Configuration → Database Connectors → NomaUBL.
  • The configuration is stored as a db-nomaubl resource in the main config file. Saving the form writes it back through the standard Settings save flow.

At a glance — Tables tab​

ConnectionTablesColumnsInitializeSchemapublicTable NamesINVOICELog / ArchiveF564230Runtime LogF564237Invoice HeaderF564231Invoice LinesF564233VAT SummaryF564234LifecycleF564235ValidationF564236E-REPORTINGE-Reporting LogF564260renumb.E-Reporting LifecycleF564261E-Reporting MapF564262NOTIFICATIONSNotificationsF564253newAUTHAuth · UsersF564250Auth · RolesF564251Auth · SessionsF564252Auth · PermissionsF564254newValidate SchemaCompares bundled DDL against the live database — read-only.✓ Validate schemaDialect: postgres Schema: public✔ OK — live schema matches bundled DDL.Last run: 2 minutes agoSame machinery as -validate-ddl CLI mode.Schemaresolves all table namesInvoice tables7 tables — header / lines / VATE-Reporting renumberedF564240/41/42 → 60/61/62NotificationsF564253, configurableAuth · 4 tablesusers / roles / sessions / perm.Validate SchemaDDL drift detector — read-only

Tab 1 — Connection​

Connection​

FieldValuesDescription
Database TypeOracle / PostgreSQLBackend type. Determines the JDBC URL format and the default placeholders.
JDBC URLtextFull JDBC connection string. Format depends on the database type: jdbc:oracle:thin:@host:1521/service_name for Oracle, jdbc:postgresql://host:5432/database_name for PostgreSQL.

Credentials​

FieldDescription
DB UserDatabase account name with the privileges required to read/write all NomaUBL tables (and create them when initializing).
DB PasswordPassword for the DB account. Stored Base64-encoded in the config file.

Tab 2 — Tables​

Schema​

FieldDescription
SchemaDatabase schema containing every NomaUBL table (e.g. public on PostgreSQL, the JDE library / owner on Oracle). All table names below are resolved within this schema.

Table Names​

NomaUBL distributes its data across 15 tables grouped by purpose. The defaults follow the JDE F564xxx naming, but any name can be used as long as the DB account has the right privileges on it.

Invoice tables​

FieldDefaultStores
Log / ArchiveF564230Persistent log / archive of every processing run.
Runtime LogF564237Live runtime log used by the in-app Processing Log viewer. New FEUKID PK, columns FERMK / FERMK2 / FEK74MSG1 (renamed in 2026.05.5).
Invoice HeaderF564231One row per invoice with its identification, totals, status, addressing data.
Invoice LinesF564233Invoice line items (when Store line subtotals is on).
VAT SummaryF564234Per-invoice VAT summary by category (when Store VAT details is on).
LifecycleF564235Status-change history of each invoice (transitions between regulatory codes).
ValidationF564236Schematron / business-rule validation results per invoice.

E-Reporting tables​

FieldDefaultStores
E-Reporting LogF564260One row per e-Reporting submission. The XML payload column is BYTEA / BLOB since 2026.05.5 (UTF-8 bytes via dialect.writeBlob).
E-Reporting LifecycleF564261Status-change history of each submission. FK column renamed to RHUKID in 2026.05.5.
E-Reporting MapF564262Per-submission mapping of original invoice → e-Reporting line. FK column renamed to RIUKID in 2026.05.5.
Schema migration

The e-Reporting tables changed numbering in 2026.05.5 — the previous F564240 / F564241 / F564242 are renamed to F564260 / F564261 / F564262. If your install pre-dates 2026.05.5 and the data is worth keeping, copy the rows over before re-running Initialize Database with the new names. The bundled DDL only creates the new names.

Notifications + Auth​

FieldDefaultStores
NotificationsF564253Inbox rows surfaced by the navbar bell and the Application → Notifications page (introduced in 2026.05.3).
Auth · UsersF564250User identities (login, hashed password, full name, active flag).
Auth · RolesF564251Role identity (name + description). The CSV grant columns from previous versions are gone — see Auth · Permissions.
Auth · SessionsF564252Active sessions: SSLSID UUID token, SSUSER, SSSTDTIM start, SSETDTIM end.
Auth · PermissionsF564254Row-based grants — PMROLE, PMCRAPPID (page / company / feature), PMCRAPPVAL, PMENABL. New in 2026.05.5.

Validate Schema​

A new section under Tables exposes a Validate schema button. It calls /api/auth/validate-ddl (same machinery as the -validate-ddl CLI mode) which compares the bundled DDL — the canonical .sql files inside the JAR — against the live schema, using the table names configured here.

The result panel reports four kinds of drift:

SectionMeaning
Missing tablesDeclared in the bundled DDL but absent in the database. Re-run Initialize Database to create them.
Missing columnsThe DDL declares a column that the database does not have. Typically appears after an upgrade that adds a column to an existing table.
Type / size mismatchesColumn exists, but with a different type or width than the bundled DDL declares. Lists expected vs actual per column.
Extra columnsThe database has a column the DDL no longer declares. Informational only — the application ignores them.

The check is read-only — it never alters the schema. A clean run prints ✔ OK — live schema matches bundled DDL.

Detail Storage​

Controls what gets saved on each invoice beyond the header. Two independent switches sit under Tables — one for invoice line subtotals, one for VAT details — so each level of detail can be turned on or off on its own.

SwitchEffect when onUsed by
Store line subtotalsWrites every line of every invoice into F564233 at insert.SQL-based reporting that needs line-level totals; the Lines tab of the invoice detail modal reads from the UBL either way.
Store VAT detailsWrites the per-rate VAT breakdown of every invoice into F564234 at insert.The VAT Declaration page — keeping this switch on is what lets the page open in seconds even on a 200 000-invoice month.

Either switch turned off keeps the corresponding detail in the stored UBL document only — the application still parses it on demand when the Lines or VAT tab of the invoice detail modal is opened.

The two switches default to whatever was previously configured. Existing installations keep their current behaviour after the upgrade — no file edit required.

Backfilling VAT details for past periods​

When Store VAT details is turned on for the first time, only new invoices land with the details in F564234. To fill F564234 for invoices that existed before the switch was turned on — the historical periods you want to see on the VAT page — use the backfill-vat command:

./nomaubl.sh backfill-vat prod 2026-04-01 2026-04-30

The command parses the UBL document already kept on each invoice header and re-inserts the per-rate breakdown — safe to re-run on the same period without creating duplicates.

Tab 3 — Columns​

The Columns tab is a per-table editor that overrides column names at runtime via UBLColumnConfig. Every Java sub-handler that reads or writes a NomaUBL column resolves its name through cols.<accessor> instead of hardcoded literals — so renaming a column in the database (and recording it here) keeps the application running without a recompile.

The editor groups overrides by table and exposes the same accessor names referenced by Java code. The default values match the JDE F564xxx columns (UHKCO, FETXFT, RGUKID, …); changing them is a customer-side concern and rarely needed unless the schema diverges from the bundled DDL.

The new build-time Cross-Reference page (Documentation → Cross-Ref) lists every Java call site that reads tables.<X> or cols.<X>, with an "unused accessors" toggle to surface dead code. Generated by XrefScanner on every build, no manual upkeep.

Tab 4 — Initialize​

One-click bootstrap of a fresh NomaUBL database.

ElementDescription
Initialize Database buttonCreates every NomaUBL table that does not yet exist and bootstraps the default admin user along with the admin and viewer roles.
Log areaStreams the output of the run line by line, with per-prefix colours: OK: green for executed statements, EXISTS: muted for already-present objects, FAIL: / ERROR: red for genuine failures. The log area now flexes to fill the available height (no more fixed 200 px cap).

The operation is safe to re-run — existing tables and users are not modified, so each post-upgrade run only creates the new tables and re-seeds missing default grants. Typical lifecycle:

  1. Fresh deployment → run Initialize Database once. Every table is created, the default admin user is seeded.
  2. Upgrade to a new NomaUBL version that adds tables (e.g. 2026.05.3 added F564253, 2026.05.5 added F564254) → run Initialize Database again. Only the missing tables are created.
  3. Wiped permissions table → drop F564254 and re-run Initialize Database. The default admin and viewer grants are re-seeded; existing role rows are untouched.

Tips & best practices​

  • Pick the database type before filling the JDBC URL. The placeholder and hint adapt to the chosen type, which avoids URL-format mistakes.
  • Use a dedicated DB account. Give it SELECT / INSERT / UPDATE / DELETE on the NomaUBL schema and CREATE TABLE permission for the initial bootstrap.
  • Keep the default F564xxx table names when sitting next to JDE. This makes joining NomaUBL tables to JDE tables in reporting tools straightforward.
  • Use the Columns tab when migrating an existing schema. A customer install that pre-dates a column rename can stay on its existing names — record the override here and the application code keeps working.
  • Run Validate schema after every upgrade. It catches missing columns introduced by the upgrade and type mismatches that would otherwise produce silent runtime errors. The check is read-only — safe to run anywhere.
  • Leave Store VAT details on when the VAT declaration page is in use. Without it the page falls back to parsing every UBL on each load — fine on a quiet month, painful on a busy quarter.
  • Turn Store line subtotals off only on volume-constrained installations. The Lines tab of the invoice detail modal still works from the UBL; only third-party SQL reporting on line items goes away.
  • Re-run Initialize after every upgrade. New NomaUBL versions occasionally add tables (notifications in 2026.05.3, permissions in 2026.05.5); the safe-to-re-run script picks them up without touching existing data.
  • The init log is colour-coded. Scan for red lines first — those are real failures that need fixing. Muted EXISTS: lines on a re-run are expected.