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.javaARC_META_DATA—cis-api/.../archive/ArcMetaData.javaSGDATA_NUMBER_SEARCH—cis-search/.../parcel/SgdataNumberSearch.javaSGDATA_SECTIONAL—cis-search/.../sectional/SgdataSectional.java
Existing Oracle tables missing columns required by source¶
HISTORICAL_RECORDS_ARCHIVE:CREATEDON,DOCUMENTTYPEID,PROJECTNAMEMAN_DRAFT_CHECKLIST:GENERAL_PLANMAN_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_REASONMAN_DRAFT_STEPS:DOC_COUNTROLES:ROLE_ID(reported by bothRoles.javaandSiteRole.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.javaARCHIVE_METADATA_SEQ—ArcMetaData.javaM_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:
- deciding ownership of shared UAM tables between API and auth;
- reconciling the 201 entity mappings against the four missing relations, ten column-drift entities and three missing sequences;
- 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
- 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.