Skip to content

Oracle/Flyway baseline derivation

This is a read-only derivation from the local cis-oracle-db container, PDB ORCLPDB1, schema SGUSER, collected on 2026-11-02. The collector is scripts/schema-baseline/inspect_oracle.py and the machine-readable result is build/reports/oracle-baseline/inspection.json.

What the live schema says

The schema contains 233 tables, 252 sequences, 92 views, 310 indexes, 212 foreign keys, 17 triggers and 18 invalid objects. Two foreign keys on CART_ITEMS are disabled. The invalid objects include legacy DB-link-backed views, missing legacy tables/sequences, and invalid migration/support program units; they are not safe evidence for a clean application baseline.

The source scan found 201 JPA entities across cis-api, cis-search, cis-workflow and cis-auth. The following is the source-authoritative reconciliation. It compares explicitly mapped entity tables/columns/sequences; it does not infer unannotated Hibernate names.

Tables mapped in source but absent from Oracle

  • ARCHIVE_MASTER — cis-api/.../archive/ArchiveMaster.java
  • ARC_META_DATA — cis-api/.../archive/ArcMetaData.java
  • SGDATA_NUMBER_SEARCH — cis-search/.../parcel/SgdataNumberSearch.java
  • SGDATA_SECTIONAL — cis-search/.../sectional/SgdataSectional.java

Existing Oracle tables missing columns required by source

  • HISTORICAL_RECORDS_ARCHIVE: CREATEDON, DOCUMENTTYPEID, PROJECTNAME
  • MAN_DRAFT_CHECKLIST: GENERAL_PLAN
  • MAN_DRAFT_REQUEST: AMENDMENT_DETAILS, BC_AUTH_DIAGRAM_SGNO, BC_BOUNDARY_NOTES, BC_COORDINATE_FRAME, BC_PURPOSE, BC_REQUESTOR_TYPE, BC_SOURCE_DOCS_JSON, BC_SUPPORTING_DOC_ID, CLOSURE_TYPE, CRE_APP_LETTER_DOC_ID, CRE_CERT_DOC_ID, CRE_CHILDREN_JSON, CRE_COMPUTED_RE_EXTENT, CRE_DEEDS_PRINT_DOC_ID, CRE_FINAL_RE_EXTENT, CRE_FINAL_RE_SOURCE, CRE_LETTER_DOC_ID, CRE_REQUESTOR_TYPE, CRT_NUMBER, NEW_RECORD_ID, NOTICE_NUMBER, NOTIFICATION_TYPE, PROCLAMATION_DATE, PROCLAMATION_NUMBER, PURPOSE_OF_SERVITUDE, REGISTRATION_DATE, RENAME_REASONID, SERVITUDE_DESCRIPTION, SERVITUDE_NUMBER, SS_NUMBER, STREET_RENAME, SUBSTITUTION_REASON, TARGET_TOWNSHIP_ID, TYPE_OF_AMENDMENTS, TYPE_OF_ENDORSEMENT, WITHDRAWAL_NOTE, WITHDRAWAL_REASON
  • MAN_DRAFT_STEPS: DOC_COUNT
  • ROLES: ROLE_ID (reported by both Roles.java and SiteRole.java)

MAN_DRAFT_REQUEST is already covered by V1__maintenance_archival_schema.sql; that migration adds the older archival/reconciliation columns and those columns are present in Oracle. The list above is the newer entity surface that the existing migration does not yet create. The table and migration must therefore be retained, with a new additive migration for only the missing source columns after their Oracle types and data/backfill rules are approved.

For the four absent tables, the source-required columns are also absent by definition. They are not repeated here as column drift; they require a table ownership/design decision first.

Sequences mapped in source but absent from Oracle

  • ARCHIVE_MASTER_SEQ — ArchiveMaster.java
  • ARCHIVE_METADATA_SEQ — ArcMetaData.java
  • M_REPORT_FILTERS_CONFIG_SEQ — ReportFilterConfiguration.java

MAN_DRAFT_REQUEST_SEQ is required by MaintenanceDraftRequest.java and is present in Oracle.

Live objects not suitable for a clean source baseline

The live schema also contains 18 invalid objects:

  • invalid program units: FN_GETDURATIONSTRING, MIGRATION, MD_META, MIGRATION_REPORT, MIGRATION_TRANSFORMER, CLEARME, ALL_MAJOR_REGION_TRIGGER
  • DB-link-dependent views: VW_STREET_GIS_DATA, V_KZN_ERF, V_KZN_FARMS, V_PTA_ERF, V_PTA_FARMS, V_WC_ERF, V_WC_FARMS
  • invalid search/legacy views: V_SGDATA_NUMBER_SEARCH_GIS, V_SGDATA_NUMBER_SEARCH_GIS_OLD, V_TOWNSHIP_ADMINISTRATION

These objects must not be copied into a clean baseline without an explicit dependency and ownership decision. The live schema also has two disabled foreign keys on CART_ITEMS (CART_ITEMS_FK1, CART_ITEMS_FK2).

Existing migration ownership

The API owns SGUSER."flyway_schema_history" and currently records baseline 0, V1, V2 and repeatable migrations. Auth owns the separate SGUSER.FLYWAY_SCHEMA_HISTORY_AUTH table and records baseline 0 through V8 plus repeatables. Auth V2 also creates shared UAM tables, so the two histories cannot be collapsed or replaced by a single dump-derived version.

Recommendation

Yes, a baseline can be derived, but the live schema is a source snapshot for reconciliation, not yet an activatable Flyway baseline. Keep the existing history tables and applied migrations immutable. Build a reviewed, versioned baseline migration only after:

  1. deciding ownership of shared UAM tables between API and auth;
  2. reconciling the 201 entity mappings against the four missing relations, ten column-drift entities and three missing sequences;
  3. separating required application objects (tables, sequences, constraints, required views/triggers) from legacy DB-link views, invalid program units, users, grants, links and business data; and
  4. replaying the candidate on an empty Oracle schema and running the API/auth migration and persistence tests.

Do not use baselineOnMigrate as proof of equivalence: it only records afer a reviewed marker for an existing database. For a new environment, pre baseline migration (for example a B... migration) while preserving the current V/R history for existing environments. Do not apply the generated DDL or change the live container from this audit.